Article Sections
Categories
Recent Article Comments
Currently Active Users
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:30Code 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;Code sql:
SELECT VALUE
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';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 DUALTO_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:43SYSDATE 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 DUALADD_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;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/2009The above list is by no means exclusive. Please feel free to add to the list and give examples.
Hey,
I am getting an error:
cannot find ucm.sql in the drive where oracle is installed.
...
Installing Stellent or Oracle Universal Content Management
Prachi on 07-23-2010