About Oracle Date and useful functions

Oracle has powerful date functions. Sometimes you need to convert it to another type or another type has to be converted to date type or you need trunc date to display only part of date or anything else.

So, you can lots of things on date type. I will try to tell some useful operations on date types.

Date type placed as 7 bytes on disk. It stores year, month, day, hour, minute, second. Timestamp type placed as 11 bytes. And it stores addition to date fraction of seconds and time zone information. Especially, it is useful for real time applications to measure where does the time pass. ( because of I used this type )

For example for 15.05.2009 18:01:00, Oracle do not store it as how it was written. It is stored as a different internal style as 7 bytes and this style of storing is advantageous when you trunc or convert it to another type or sort date datas.

Formatting date is useful and necessary. When you rely on the default date format, your application may be in the future affected negatively ( I have experienced it twice 🙂 ).

First and most known function is to_date and to_char functions.
For DATE type, you can convert this date type to char :

TO_CHAR(date,’DATEFORMAT’);
If you do not specify the date format, your date type will be trunced.

SQL> select to_Char(sysdate) from dual;

TO_CHAR(SYSDATE)
—————-
10/06/2009

What is the date format? What part of dates can be specified?
There are lots of documents on web that mentions about it and i don’t repeat it
( Oracle Date Parts that can be extracted ). Most known and used of them are DD, MM, YYYY, HH24, HH, MI, SS.

For example :

SQL> select to_Char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’DD/MM/YYYYHH2
——————————
10/06/2009 23:39:39

So far, we saw that how date type can be converted to char. Inversely, convert char to date is similar to it.

SQL> select to_date(’12:00:00 04.06.2009′,’HH24:MI:SS DD.MM.YYYY’) from dual;

TO_DATE(’12:00:0004.06.2009′,’
——————————
6/4/2009 12:00:00 PM

Date Arithmetics and Useful other Date functions:

Adding X days to a date you use “datevariable + 1” : SYSDATE + 1
Adding X seconds to a date you use “datevariable + 1/(24*60*60)” : SYSDATE + 1/(24*60*60)
Another adding or substracting operations you can use your mind 🙂

Adding months you can use ADD_MONTHS(variable,#months):

SQL> select sysdate, add_months(sysdate,1), add_months(add_months(sysdate,1),1) from dual;

SYSDATE ADD_MONTHS(SYSDATE,1) ADD_MONTHS(ADD_MONTHS(SYSDATE,
———– ——————— ——————————
6/10/2009 1 7/10/2009 11:55:22 PM 8/10/2009 11:55:22 PM

Format is : MONTH DAY YEAR HH24 MI SS

No difference, if current month has 30 or 31 days only month number will increase 1. If we are in the 30 January, according to year add_months(date,1) will return 28-Feb or 29-Feb.

Months_Between :

SQL> select months_between(sysdate+75,sysdate) from dual;

MONTHS_BETWEEN(SYSDATE+75,SYSD
——————————
2.45161290322581

You can trunc it to 2.

First Day Of Month :

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,’MONTH’),’DAY’) FROM DUAL;

TO_CHAR(TRUNC(SYSDATE,’MONTH’)
——————————
MONDAY

Last Day Of Month :

SQL> SELECT TO_CHAR(LAST_DAY(SYSDATE),’DAY’) FROM DUAL;

TO_CHAR(LAST_DAY(SYSDATE),’DAY
——————————
TUESDAY

When next first Friday :

SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,’FRIDAY’),’DD’) FROM DUAL;

TO_CHAR(NEXT_DAY(SYSDATE,’FRID
——————————
12

First day of Month

SQL> SELECT TO_CHAR(LAST_DAY(add_months(SYSDATE,-1))+1,’DAY’) FROM DUAL;

TO_CHAR(LAST_DAY(ADD_MONTHS(SY
——————————
PAZARTESI

Now, which # of week we are in :

SQL> select to_Char(sysdate,’IW’) from dual;

TO_CHAR(SYSDATE,’IW’)
———————
24
SQL>

EXTRACT
To extract specified date part from date or timestamp variable. Alternative way is convert date to char and perform substr operation. Former takes less time than latter.

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

SQL> select extract(hour from systimestamp)||’:’||extract(minute from systimestamp)||’:’||extract(second from systimestamp) from dual;

EXTRACT(HOURFROMSYSTIMESTAMP)|
——————————————————————————–
7:40:7,142039

HOUR, SECOND, MINUTE, TIMEZONE_HOUR, TIMEZONE_MINUTE can be extracted only timestamp.

A number that how many days, hours, minutes, seconds : NUMTODSINTERVAL(num,):

SQL> select numtodsinterval(1440,’MINUTE’) FROM DUAL;

NUMTODSINTERVAL(1440,’MINUTE’)
——————————
+000000001 00:00:00

1440 minutes equals to 1 day ( leftmost piece ).

SQL> SELECT NUMTODSINTERVAL(3602,’SECOND’) FROM DUAL;

NUMTODSINTERVAL(3602,’SECOND’)
——————————
+000000000 01:00:02

3602 seconds equals to 1 hour 2 seconds.

About NLS_DATE

To set default date format for session based.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY MM DD’;

Session altered

SQL> select sysdate from dual;

SYSDATE
———–
6/17/2009 1

SQL>

Converting unix timestamp to Oracle in PL/SQL
This is may useful your application to convert unix timestamp to oracle date.
Read this post
( Responsibility of adding or subtracting GMT is belong to you )

Another DATE functions :

# NUMTOYMINTERVAL, converts a NUMBER or expression into a YEAR TO MONTH interval.
# TO_DSINTERVAL converts string to DAY TO SECOND interval.
# TO_YMINTERVAL converts a character string to YEAR TO MONTH type