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.
- SYSDATE and CURRENT_DATE
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.
SELECT SESSIONTIMEZONE FROM DUAL; SESSIONTIMEZONE +05:30
ALTER SESSION SET TIME_ZONE = '-2:0'
As the name suggests, this determines the DATE format. If you are on SQL Plus, you can check the current nls_date_format by
SHOW PARAMETER NLS_DATE_FORMAT;
SELECT VALUE FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
TO_DATE converts a date value as a string into the Oracle DATE format.
SELECT TO_DATE ('24-MAY-2009) FROM DUAL
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
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.
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.
SELECT SYSDATE+1 FROM DUAL
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.
SELECT ADD_MONTHS (SYSDATE, 2) FROM DUAL;
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.
SELECT TO_CHAR (SYSDATE + INTERVAL '10' HOUR, 'HH:MI:SS') FROM DUAL;
This gives the LAST Day of the month.
This gives the months between two date values.
SELECT MONTHS_BETWEEN (SYSDATE + 31, SYSDATE) FROM DUAL;
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.
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.