Oracle PL/SQL Date functions

 All examples given here selects from DUAL table and DUAL table is a special one row table present by default in all Oracle database installations. All examples have been executed based on sysdate and these SQLs were run on 2/7/2011.

sysdate function:
You can use sysdate function to get current date and time .
select sysdate from dual;
return 2/7/2011 7:31:31 AM
to_char function:
You can use to_char function to convert a date to a formatted string.
select to_char(sysdate, ‘yyyy/mm/dd’) from dual;
return 2011/02/07
select to_char(sysdate, ‘DDth MON, YYYY’) from dual;
return 07TH FEB, 2011
select to_char(sysdate, ‘YYYY ,DDth MON’) from dual;
return 2011 ,07TH FEB
select to_char(sysdate, ‘MON DDth, YYYY’) from dual;
return FEB 07TH, 2011
select to_char(sysdate, ‘FMMON DDth, YYYY’) from dual;
FEB 7TH, 2011
select to_char(sysdate, ‘FMMon ddth, YYYY’) from dual;
Feb 7th, 2011
select to_char(sysdate, ‘Month DD, YYYY’) from dual;
February  07, 2011
select to_char(sysdate, ‘FMMonth DD, YYYY’) from dual;
February 7, 2011

to_date function:
You can use to_date function to convert a string to a Date.
select to_date(‘2011/02/07’, ‘yyyy/mm/dd’) from dual;
return 2/7/2011
select to_date(‘070211′,’DDMMYY’) from dual;
return 2/7/2011
trunc function:
You can use trunc function to return a date truncated to a specific unit of measure.
Get beginning of the quarter
select trunc(sysdate, ‘Q’) from dual;
return 1/1/2011
Get first day of the Month
select trunc(sysdate, ‘MONTH’) from dual;
return 2/1/2011
Get start day of the week or get first work day of the week
select trunc(sysdate,’DD’) from dual;
return 2/7/2011
Get time in 24 hours
select trunc(sysdate,’HH24′) from dual;
return 2/7/2011 7:00:00 AM
Get time in 12 hours
select trunc(sysdate,’HH12′) from dual;
return 2/7/2011 7:00:00 AM

add_months function:
You can use add_months function to add or subtract n months from a given date.
select add_months(sysdate,2) from dual;
return 4/7/2011 7:15:49 AM
select add_months(sysdate,-4) from dual;
return 10/7/2010 7:16:47 AM
last_day function:
You can use last_day function to return last day of the current month.
select last_day(sysdate) from dual;
return 2/28/2011 7:23:49 AM

next_day function:
You can use next_day function to get the next week day greater than     the given date.
Today is 02/07/2011 Monday.
select next_day(sysdate,’MONDAY’) from dual;
return 2/14/2011 7:26:36 AM – Returns next Monday
select next_day(sysdate,’TUESDAY’) from dual;
2/8/2011 7:28:23 AM – Returns next Tuesday
select next_day(sysdate,’WEDNESDAY’) from dual;
2/9/2011 7:28:23 AM – Returns next Wednesday
select next_day(sysdate,’THURSDAY’) from dual;
2/10/2011 7:28:23 AM – Returns next Thursday
select next_day(sysdate,’FRIDAY’) from dual;
2/11/2011 7:28:23 AM – Returns next Friday
current_date function:
You can use current_date function to return current date and time in the current time zone.
select current_date from dual;
return 2/7/2011 7:19:07 AM
current_timestamp function:
You can use current_timestamp function to return current date and and time with the local time zone.
select current_timestamp from dual;
return 07-FEB-11 AM -06:00;

Leave a Reply