Date Functions in Oracle SQL

 3shTech (trainings@trishtechnology.com)

String Functions

Numeric Functions

Group Functions

Date Functions

Analytical Functions

Date Functions

·       Oracle default date format is DD-MON-YYYY.

·       We can change the default format to our desired format by using the following command.
But this will expire once the session was closed.

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

Below are the Oracle DATE FUNCTIONS used to manipulate the date values:-

 

·       SYSDATE

·       CURRENT_DATE

·       CURRENT_TIMESTAMP

·       SYSTIMESTAMP

·       LOCALTIMESTAMP

·       DBTIMEZONE

·       SESSIONTIMEZONE

·       TO_CHAR

·       TO_DATE

·       ADD_MONTHS

·       MONTHS_BETWEEN

·       NEXT_DAY

·       LAST_DAY

·       EXTRACT

·       GREATEST

·       LEAST

·       ROUND

·       TRUNC

·       NEW_TIME

·       COALESCE

 
SYSDATE:

·       This will give the current date and time.

Example:–
 SQL>SELECT SYSDATE FROM DUAL;

 
 
CURRENT_DATE:

·       This will returns the current date in the session’s time zone.

Example:–
SQL>SELECT CURRENT_DATE FROM DUAL;

 
 
CURRENT_TIMESTAMP:
 

·       This will returns the current timestamp with the active time zone information.

Example:–
SQL>SELECT CURRENT_TIMESTAMP FROM DUAL;

 
 
SYSTIMESTAMP:
 

·       This will returns the system date, including fractional seconds and time zone of the Database.

Example:–
SQL>SELECT SYSTIMESTAMP FROM DUAL;

 
 
LOCALTIMESTAMP:
 

·       This will returns local timestamp in the active time zone information, with no time zone information shown.

Example:–
 SQL>SELECT LOCALTIMESTAMP FROM DUAL;

 
 
DBTIMEZONE:

·       This will returns the current database time zone in UTC format. (Coordinated Universal Time).

Example:–
 SQL>SELECT DBTIMEZONE FROM DUAL;

 
 
SESSIONTIMEZONE:
 

·       This will return the value of the current session’s time zone.

Example:–
 SQL>SELECT SESSIONTIMEZONE FROM DUAL;

 
 
TO_CHAR:
 

·       This will be used to EXTRACT various date formats.

·       The available date formats as follows.

Syntax: – TO_CHAR (date, format)

DATE FORMATS:
 

Date Format	Description
DD	No of days in month
DDD	No of days in year
MM	No of month
MON	Three letter abbreviation of month
MONTH	Fully spelled out month
RM	Roman numeral month
DY	Three letters abbreviated day
DAY	Fully spelled out day
Y	Last one digit of the year
YY	Last two digits of the year
YYY	Last three digits of the year
YYYY	Full four digit year
SYYYY	Signed year
I	One digit year FROM ISO standard
IY	Two digit year from ISO standard
IYY	Three digit year from ISO standard
IYYY	Four digit year from ISO standard
Y, YYY	Year with comma
YEAR	Fully spelled out year
CC	Century
Q	No of quarters
W	No of weeks in month
WW	No of weeks in year
IW	No of weeks in year from ISO standard
HH	Hours
MI	Minutes
SS	Seconds
FF	Fractional seconds
AM or PM	Displays AM or PM depending upon time of day
A.M or P.M	Displays A.M or P.M depending upon time of day
AD or BC	Displays AD or BC depending upon the date
A.D or B.C	Displays AD or BC depending upon the date
FM	Prefix to month or day, suppresses padding of month or Day.
TH	Suffix to a number
SP	suffix to a number to be spelled out
SPTH	Suffix combination of TH and SP to be both spelled out
THSP	same as SPTH

 
Examples:–
 SQL>SELECT TO_CHAR (SYSDATE,’dd-mm-yyyy hh24: mm:ss’) FROM DUAL;

 SQL>SELECT TO_CHAR (SYSDATE,’dd month year’) FROM DUAL;

 
 
TO_DATE:
 

·       This will be used to convert the string into date format.

Syntax: – TO_DATE (date)

Example:–
SQL>SELECT TO_CHAR (TO_DATE (’24/dec/2006′,’dd/mon/yyyy’), ‘dd * month * day’) date_format FROM DUAL; 

— If you are not using TO_CHAR oracle will display output in default date format.
 
ADD_MONTHS:
 

·       This will add the specified months to the given date.

Syntax: – ADD_MONTHS (date, no_of_months)

Example:–
SQL>SELECT ADD_MONTHS (SYSDATE, 5) FROM DUAL;

SQL>SELECT ADD_MONTHS (SYSDATE,-5) FROM DUAL;
<img alt="" src="http://newtonapples.com/old/Old/wp-content/uploads/2012/09/092112_1802_DateFunctio12.png" />
<ul>
<li>If no_of_months is zero then it will display the same date.</li>
</ul>
SQL>SELECT ADD_MONTHS (SYSDATE, 0) FROM DUAL;

 

·       If no_of_months is null then it will display nothing.

SQL>SELECT ADD_MONTHS (SYSDATE, null) FROM DUAL;

 
MONTHS_BETWEEN:

·       This will give difference of months between two dates.

Syntax: – MONTHS_BETWEEN (date1, date2)

Example:–
SQL>SELECT MONTHS_BETWEEN (ADD_MONTHS (SYSDATE, 5), SYSDATE) FROM DUAL;

SQL>SELECT MONTHS_BETWEEN (TO_DATE (’11-aug-1990′,’dd-mon-yyyy’), TO_DATE (’11- jan-1990′,’dd-mon-yyyy’)) mon_bet FROM DUAL;

 
 
NEXT_DAY:
 

·       This will produce next day of the given day FROM the specified date.

Syntax: – NEXT_DAY (date, day)

Example:–
SQL>SELECT NEXT_DAY (SYSDATE,’TUE’) FROM DUAL;

 
 
LAST_DAY:
 

·       This will produce last day of the given date.

Syntax: – LAST_DAY (date)

Example:–
SQL>SELECT LAST_DAY (SYSDATE) FROM DUAL;

 
 
EXTRACT:
 

·       This is used to EXTRACT a portion of the date value.

Syntax: – EXTRACT ((year | month | day | hour | minute | second) FROM date)

Example:–
 SQL>SELECT EXTRACT (year FROM SYSDATE) FROM DUAL;

 SQL>SELECT EXTRACT (month FROM SYSDATE) FROM DUAL;

 SQL>SELECT EXTRACT (day FROM SYSDATE) FROM DUAL;

 
 
GREATEST:
 

·       This will give the GREATEST date.

·       It will take ‘n’ number of arguments but data type should be same.

Syntax: – GREATEST (date1, date2….daten)

Example:–

 SQL>SELECT GREATEST (TO_DATE (’11-jan-1990′,’dd-mon-yyyy’), TO_DATE (’11-mar-1990′,’dd-mon-yyyy’), TO_DATE (’11-apr-[/sql]
[sql] 1990′,’dd-mon-yyyy’)) FROM DUAL;

 

·       If all the values are zeros then it will display zero.

·       If all the parameters are nulls then it will display nothing.

·       If any of the parameters is null it will display nothing.

 
 
LEAST:
 

·       This will give the LEAST date.

·       It will take ‘n’ number of arguments but data type should be same.

Syntax: – LEAST (date1, date2….daten)

Example:–

SQL>SELECT LEAST(TO_DATE(’11-jan-1990′,’dd-mon-yyyy’),TO_DATE(’11-mar-1990′,’dd-mon-yyyy’),TO_DATE(’11-apr-1990′,’dd-mon-yyyy’)) least_date FROM DUAL;

 

·       If all the values are zeros then it will display zero.

·       If all the parameters are nulls then it will display nothing.

·       If any of the parameters is null it will display nothing.

 
 
ROUND:
 

·       ROUND will ROUNDs the date to which it was equal to or greater than the given date.

Syntax: – ROUND (date, (day | month | year))

·       If the second parameter was year then ROUND will checks the month of the given date in

The following ranges.
 

JAN    —    JUN
 

   JUL     —    DEC
 

·       If the month falls between JAN and JUN then it returns the first day of the current year.

·       If the month falls between JUL and DEC then it returns the first day of the next year.

·       If the day falls between 1 and 15 then it returns the first day of the current month.

·       If the day falls between 16 and 31 then it returns the first day of the next month.

·       If the second parameter was day then ROUND will checks the week day of the given date

·       In the following ranges.

SUN    —    WED
 

   THU     —    SUN
 

·       If the week day falls between SUN and WED then it returns the previous Sunday.

·       If the weekday falls between THU and SUN then it returns the next Sunday.

·       If the second parameter was null then it returns nothing.

·       If we are not specifying the second parameter then ROUND will resets the time to the beginning of the current day in case of user specified date.

·       If we are not specifying the second parameter then ROUND will resets the time to the beginning of the next day in case of SYSDATE.

Example:–

SQL>SELECT ROUND (TO_DATE (’24-dec-04′,’dd-mon-yy’),’year’), ROUND (TO_DATE (’11-mar-06′,’dd-mon-yy’),’year’) FROM DUAL;

SQL>SELECT ROUND (TO_DATE (’11-jan-04′,’dd-mon-yy’),’month’), ROUND (TO_DATE (’18-jan-04′,’dd-mon-yy’),’month’) FROM DUAL;

SQL>SELECT ROUND (TO_DATE (’26-dec-06′,’dd-mon-yy’),’day’), ROUND (TO_DATE (’29-dec-06′,’dd-mon-yy’),’day’) FROM DUAL;

 
 
TRUNC:
 

·       TRUNC will chops off the date to which it was equal to or less than the given date.

Syntax: – TRUNC (date, (day | month | year))

·       If the second parameter was year then it always returns the first day of the current year.

·       If the second parameter was month then it always returns the first day of the current month.

·       If the second parameter was day then it always returns the previous Sunday.

·       If the second parameter was null then it returns nothing.

·       If  you are not specifying the second parameter then trunk will resets the time to the beginning of the current day.

SQL>SELECT TRUNC (TO_DATE (’24-dec-04′,’dd-mon-yy’),’year’), TRUNC (TO_DATE (’11-mar-06′,’dd-mon-yy’),’year’) FROM DUAL;

SQL>SELECT TRUNC (TO_DATE (’11-jan-04′,’dd-mon-yy’),’month’), TRUNC (TO_DATE (’18-jul-04′,’dd-mon-yy’),’month’) FROM DUAL;

SQL>SELECT TRUNC (TO_DATE (’26-dec-06′,’dd-mon-yy’),’day’), TRUNC (TO_DATE (’29-dec-06′,’dd-mon-yy’),’day’) FROM DUAL;

 

NEW_TIME:
 

·       This will give the desired time zone’s date and time.

Syntax: NEW_TIME (date, current_timezone, desired_timezone)

TIMEZONES
 

       AST/ADT    —    Atlantic standard/day light time.

BST/BDT    —    Bering standard/day light time.

CST/CDT    —    Central standard/day light time.

EST/EDT    —    Eastern standard/day light time.

GMT         —    Greenwich mean time.

HST/HDT    —    Alaska-Hawaii standard/day light time.

MST/MDT    —    Mountain standard/day light time.

NST      —    Newfoundland standard time.

PST/PDT    —    Pacific standard/day light time.

YST/YDT    —    Yukon standard/day light time.

Example:-

SQL>SELECT TO_CHAR (NEW_TIME (SYSDATE,’gmt’,’yst’),’dd mon yyyy hh: mi: ss am’) FROM DUAL;

SQL>SELECT TO_CHAR (NEW_TIME (SYSDATE,’gmt’,’est’),’dd mon yyyy hh: mi: ss am’) FROM DUAL;

 
 
COALESCE:
 

·       This will give the first non-null date.

Syntax: – COALESCE (date1, date2, date3….daten)

Example:–
SQL>SELECT COALESCE (’12-jan-90′,’13-jan-99′), COALESCE (null,’12-jan-90′,’23-mar-98′, null) FROM DUAL;