Re: Extracting date fields from a DATE data type

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 11 Jul 1998 15:14:58 GMT
Message-ID: <35a780e2.1372423@192.86.155.100>

A copy of this was sent to rrachamallu_at_my-dejanews.com (if that email address didn't require changing) On Fri, 10 Jul 1998 23:01:06 GMT, you wrote:

>Hi, I need to extract the date fields (year, month, day, hour, minute,
>second) from a date field. I could probably convert the date to char, do a
>substring operation to get the needed field, and convert it back to number.
>But this is very awkward. Is there any function that does this kind of thing
>directly?
>

Look at the to_char function documented in the sql lang ref manual, chapter 3 (all of the formats are there).... for example:

SQL> select to_char(sysdate, 'yyyy' ), to_char(sysdate, 'MM'),         
  2         to_char(sysdate, 'dd' ), to_char(sysdate,'hh24'),
  3         to_char(sysdate, 'mi' ), to_char(sysdate,'ss')

4 from dual;

TO_C TO TO TO TO TO
---- -- -- -- -- --
1998 07 11 11 12 37

gives you the year, month, day, hour, minute and seconds... You can to_number any of these to convert into a number.... SQL>
>thanks.
>reddy
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jul 11 1998 - 10:14:58 CDT