Oracle Date Functions I wanted to compile a list of all possible DATE functions in oracle. I am listing a few common ones to begin with, and I look forward to other members adding to this list. SESSIONTIMEZONE NLS_DATE_FORMAT TO_DATE TO_CHAR SYSDATE and CURRENT_DATE +/- ADD_MONTHS INTERVAL LAST_DAY MONTHS_BETWEEN TRUNC SESSIONTIMEZONE These are the oracle variables which determine your current time and it's format. sessontimezone decides your time zone. The code below gives the SESSIONTIMEZONE. Code (SQL): SELECT SESSIONTIMEZONE FROM DUAL; SESSIONTIMEZONE +05:30 We can change the zone by a simple ALTER command as below Code (SQL): ALTER SESSION SET TIME_ZONE = '-2:0' NLS_DATE_FORMAT As the name suggests, this determines the DATE format. If you are on SQL Plus, you can check the current nls_date_format by Code (SQL): SHOW PARAMETER NLS_DATE_FORMAT; You can also find the format by the following query. Code (SQL): SELECT VALUE FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT'; To change the format, use the following ALTER query Code (SQL): ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; TO_DATE TO_DATE converts a date value as a string into the Oracle DATE format. Code (SQL): SELECT TO_DATE ('24-MAY-2009) FROM DUAL TO_CHAR TO_CHAR isn't exactly a DATE function, but it helps immensely when we want to display our dates in a particular format. We need to give the format in which we want the date output as the second argument to this function Code (SQL): SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM DUAL; 24-MAY-2009 10:28:43 SYSDATE and CURRENT_DATE SYSDATE returns current date/time for the server, in the server's local time while CURRENT_DATE returns current date/time for the server, in the client's/connection's local time. Code (SQL): SELECT CURRENT_DATE FROM DUAL SELECT SYSDATE FROM DUAL +/- We can perform plus/minus operations on date values whereby we can add or subtract number of days from dates. The following will give you tomorrow's date. Code (SQL): SELECT SYSDATE+1 FROM DUAL ADD_MONTHS ADD_MONTHS takes two arguments, the first being the date value and second the number of months to add to that date value. We can use negative values to subtract months. Some people ask if there is something like ADD_YEAR. Well no there isn't, why not just give the second argument in multiples of 12. Code (SQL): SELECT ADD_MONTHS (SYSDATE, 2) FROM DUAL; INTERVAL INTERVAL is used to add or subtract a specific interval in SECOND, MINUTE or HOUR to a date value. We can specify an optional date format to display the output in. Code (SQL): SELECT TO_CHAR (SYSDATE + INTERVAL '10' HOUR, 'HH:MI:SS') FROM DUAL; You can change the HOUR to MINUTE or SECOND. LAST_DAY This gives the LAST Day of the month. MONTHS_BETWEEN This gives the months between two date values. Code (SQL): SELECT MONTHS_BETWEEN (SYSDATE + 31, SYSDATE) FROM DUAL; TRUNC TRUNC is a very useful function and it truncates a date value to give only the date, month and year and not the minutes and seconds. Code (SQL): SELECT SYSDATE FROM DUAL; 5/24/2009 10:54:49 PM SELECT TRUNC (SYSDATE) FROM DUAL; 5/24/2009 The above list is by no means exclusive. Please feel free to add to the list and give examples.