Cool SQL function: EXTRACT
I have always used the SQL function TO_CHAR to extract parts of a date, like the month or the year. But, there is another way to extract parts of a date: the function EXTRACT. Here is an example:
Using TO_CHAR:
HR@XE> select
2 to_char(to_date('05-18-2005','mm-dd-yyyy'),'mm') mm
3 from dual
4 /
MM
--
05
Of course, the datatype of the returned value is character.
Using EXTRACT:
HR@XE> select
2 extract(month from to_date('05-18-2005','mm-dd-yyyy')) mm
3 from dual
4 /
MM
----------
5
Here, the datatype of the returned value is number.
The syntax of the EXTRACT function is:
EXTRACT( { { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
}
| { TIMEZONE_HOUR
| TIMEZONE_MINUTE
}
| { TIMEZONE_REGION
| TIMEZONE_ABBR
}
}
FROM { datetime_value_expression
| interval_value_expression
}
)
EXTRACT is supported in Oracle DB versions 9i and 10g. Moreover, even though I could not find a mention of this function in the 8i documentation, I did test the above query in 8.1.7 and it worked. Does this make EXTRACT an undocumented function in 8i? or does it mean that I did not look hard enough in the 8i documentation?
Related articles:
- Cool SQL function: DUMP
- Cool SQL Analytic Function: RATIO_TO_REPORT
- Cool Undocumented SQL Function: REVERSE
For an unknown reason you cannot extract Hour, minute or second from a date. It works only on Timestamp. Francois
interesting is extracting nanoseconds from timestamp :
SQL> create table t as select timestamp ’2000-01-01 00:00:42.123456789′ t from dual;
Table created.
SQL> select mod(extract(second from t),1)*1000000000 ns from t; NS
123456789
You can also get this information with the To_Char() function.
SELECT to_char(SYSTIMESTAMP,’FF’) FROM dual
you cannot extract Hour, minute or second from a date
Yes, the documentation says: The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value.
I’m not sure exactly why you cannot get to the hour/minute/second from a DATE using EXTRACT, since a DATE datatype contains a time portion as well (but not fractional seconds).
It’ll be interesting to know what the reason behind this limitation is.
Yes, I just used this the other day. I wrapped it in a DECODE to get a text representation of the month.
Well, this isn’t so useful if you want the name of the day instead of a number. Say, “MON” instead of 2. Back to TO_CHAR!
You can cast a date to a timestamp and then extract the hour.
@Gary: Good idea. Here is an example
select extract(hour from cast(sysdate as timestamp)) hh, extract(minute from cast(sysdate as timestamp)) mi, extract(second from cast(sysdate as timestamp))ss from dualThanks.