ORACLE DATE & TIME + CONVERSION FUNCTIONS WITH EXAMPLES

ORACLE DATE & TIME FUNCTIONS
To see the system date and time use the following functions:

CURRENT_DATE    : Returns the current date in the session time zone.

SYSDATE                  : Returns the current date.

SYSTIMESTAMP     : The SYSTIMESTAMP function returns the system date, including fractional seconds and time zone of the database. The return

                                      type is TIMESTAMP WITH TIME ZONE.

 select CURRENT_DATE, SYSDATE, SYSTIMESTAMP from dual;

We have some format below to do the format masking with Date  & Time functions

We have some SUFFIXES too

--

Below are the functions that we can use with dates
TO_DATE(value, format_mask)                     - For converting string into date format

TO_CHAR(value, format_mask)                    - For converting number or date into string format

ADD_MONTHS(date1,n)                     - For adding/substracting months with the mentioned date

MONTHS_BETWEEN (date1, date2) - For calculating the months(in numeric form) between two dates

NEXT_DAY (date, dayname)                - For calculating the next coming same day's date

LAST_DAY (date)                                  - For calculating the last date of the month according to the mentioned date

ROUND(date,format) - For rounding the date, it has some rules. DEFAULT it will round the nearest day. Below are the rules & examples

TRUNC(date,format) - For truncating the date, DEFAULT it will truncate TIME portion. This function will not round anything

TO_TIMESTAMP(SYSDATE,'MM/DD/YYYY')          - Converts A String To A Timestamp
TO_TIMESTAMP_TZ(SYSDATE,'MM/DD/YYYY')   - Converts A String To A Timestamp With Timezone
SYSTIMESTAMP                                                           - Returns Current System Date And Time (Including Fractional Seconds And Time Zone)
LOCALTIMESTAMP                                                     - Returns Current Date And Time In The Time zone Of The Current SQL Session
DBTIMEZONE                                                               - Returns Database Time Zone As A Time Zone Offset (Format: '[+|-]TZH:TZM')
SESSIONTIMEZONE                                                    - Returns Current Session's Time Zone As A Time Zone Offset (Format: '[+|-]TZH:TZM')
TO_YMINTERVAL('01-05') "INTERVAL Y2M"        - Convert To An INTERVAL YEAR TO MONTH
 




*************************************************************************************************************************************************************************************
IF YOU WANT TO CALCULATE THE TIME DIFFERENCE IN BETWEEN TWO DATES

For Number Of DAYS                    : TO_DATE(date1,'MM/DD/YYYY HH:MI:SS') - TO_DATE(date2date1,'MM/DD/YYYY HH:MI:SS')
   
For Number Of HOURS                 : (TO_DATE(date1,'MM/DD/YYYY HH:MI:SS') - TO_DATE(date2date1,'MM/DD/YYYY HH:MI:SS')) * 24

For Number Of MINUTES             : (TO_DATE(date1,'MM/DD/YYYY HH:MI:SS') - TO_DATE(date2date1,'MM/DD/YYYY HH:MI:SS')) * 24*60

For Number Of SECONDS            : (TO_DATE(date1,'MM/DD/YYYY HH:MI:SS') - TO_DATE(date2date1,'MM/DD/YYYY HH:MI:SS')) * 24*60*60

For Number Of MILLISECONDS : (TO_DATE(date1,'MM/DD/YYYY HH:MI:SS') - TO_DATE(date2date1,'MM/DD/YYYY HH:MI:SS')) * 24*60*60*1000
*************************************************************************************************************************************************************************************










NOTE: IF YOU HAVE DOUBTS, KINDLY CONTACT ME VIA THIS BLOG