Re: Oracle equivalent to DATEPART()?

John Connelly <xwhiz_at_netcom.com> wrote:

>Does Oracle have an equivalent to Sybase's DATEPART function? Please don't
>flame me, I've only used Sybase. Ideally, what I want to do is group by
>months of a year or by hours of a day, something like:
 

>select datepart(hour, itemtime), count(items)
>from tablename
>group by datepart(hour, itemtime)
 

>How should I do this in Oracle? Can I use casting of the timestamp?
 

>Thanks,
>John
 

> xwhiz_at_netcom.com

Use the TO_CHAR function. For example, to extract just the month portion of a date column named ITEMTIME, you would say TO_CHAR(ITEMTIME,'MM') -- which comes back with a numeric month with leading zeros if appropriate.

For the hour, it's TO_CHAR ( ITEMTIME , 'HH' ) or better yet use HH24 to get the 24-hour clock where 1 p.m. is shown as 13.

The first argument of TO_CHAR is the column or whatever; the second is the format mask. You can build your own format mask using the elements provided, such as HH:MI for hours:minutes, DD-Mon-YY for 03-Jan-95, etc.

Hope this helps. I don't have the manual in front of me, so typos and syntax errors in the above are entirely possible ;-).

Cheers.

 -Tom

-- 
    Thomas Cox     tcox_at_netcom.com      503-293-8474
Senior Consultant, Moss Consulting Group, Portland, Oregon