A collection of conversions, formats, math, generators, and conditions for date and timestamp data types.
I’ll show most examples as simple SQL statements from DUAL. Many of the expressions could be used directly in PL/SQL without a query. Unless otherwise specified my examples have NLS_DATE_LANGUAGE=American. If that is not the case for your sessions you may need to change the names or abbreviations of days and months. For example, “Saturday” in English vs “Samstag” in German, or “Sat” vs “Sa” for their corresponding abbreviations.
Most examples will use SYSDATE or SYSTIMESTAMP for the date/time input but the expressions could be used with your own function, variable, or column value of the same data type.
If using SQL*Plus, sqlcl, or similar text-based tools I recommend the following settings for these examples to ensure the text results are displayed fully:
alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'; alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ssXff6'; alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd hh24:mi:ssXff6 TZR';
The BASICS
Date and time of database server – returned as a DATE type
select sysdate from dual;
Date and time of database server – returned as a TIMESTAMP WITH TIME ZONE type
select systimestamp from dual;
Date and time of database server, converted to your session’s time zone – returned as a DATE type.
select current_date from dual;
Date and time of database server, converted to your session’s time zone – returned as a TIMESTAMP WITH TIME ZONE type.
select current_timestamp from dual;
Text of the name of the day for a given DATE or TIMESTAMP. By default the name will be right padded with spaces to maximum length of any day based on NLS settings. Use the “fm” format prefix to toggle padding. The text is returned as a VARCHAR2 type.
SELECT TO_CHAR(SYSDATE, 'Day') FROM DUAL; SELECT TO_CHAR(SYSDATE, 'fmDay') FROM DUAL;
Text of the abbreviation for the day of a given DATE or TIMESTAMP. The text is returned as a VARCHAR2 type.
SELECT TO_CHAR(SYSDATE, 'Dy') FROM DUAL;
Text of the abbreviation for the day of a given DATE or TIMESTAMP value, overriding the current session’s default NLS settings. The text is returned as a VARCHAR2 type.
SELECT TO_CHAR(SYSDATE,'Dy','NLS_DATE_LANGUAGE=English') FROM DUAL; SELECT TO_CHAR(SYSDATE,'Dy','NLS_DATE_LANGUAGE=Spanish') FROM DUAL; SELECT TO_CHAR(SYSDATE,'Dy','NLS_DATE_LANGUAGE=German') FROM DUAL;
Detailed description of a DATE or TIMESTAMP including the spelled out names of the day of the week and month as well as day of month and year. For example: “Sunday November 1, 2020” the names will be right padded. The text is returned as a VARCHAR2 type.
SELECT TO_CHAR(SYSDATE, 'Day Month dd, yyyy')
FROM DUAL;
Spell out names of the day of the week and month of a DATE or TIMESTAMP. For example: “Sunday November 1, 2020” the name padding will be removed.The text is returned as a VARCHAR2 type.
SELECT TO_CHAR(SYSDATE, 'fmDay Month dd, yyyy') FROM DUAL;
SELECT TO_CHAR(SYSDATE,
'fmDay Month dd, yyyy',
'NLS_DATE_LANGUAGE=Spanish')
FROM DUAL;
Determine if a DATE or TIMESTAMP falls on a Weekend or a Weekday. The text is returned as a VARCHAR2 type.
SELECT CASE
WHEN TO_CHAR(SYSDATE, 'Dy', 'NLS_DATE_LANGUAGE = English') IN
('Sat', 'Sun')
THEN
'Weekend'
ELSE
'Weekday'
END
FROM DUAL;
When truncating a DATE or TIMESTAMP value, it’s important to remember the resulting value still has a time component even if you don’t display it. Also the result is always a DATE type, even if the input is any form of a TIMESTAMP.
Truncate or “round down” to 00:00:00 (12:00:00am) of the given input value, result is a DATE.
SELECT TRUNC(SYSDATE),
TRUNC(SYSTIMESTAMP),
TRUNC(CURRENT_TIMESTAMP),
TRUNC(CURRENT_DATE)
FROM DUAL;
Truncate to 00:00:00 (12:00:00am) of the beginning of the week. Unfortunately the TRUNC function does not support a parameterized NLS_TERRITORY choice. So, while TRUNC will observe different week-starts, you need to alter your session first.
alter session set nls_territory=FRANCE; SELECT TRUNC(SYSDATE,'d') from dual; alter session set nls_territory=AMERICA; SELECT TRUNC(SYSDATE,'d') from dual;
Truncate to 00:00:00 (12:00:00am) of the beginning of the ISO week. ISO weeks always start on a Monday.
SELECT TRUNC(SYSDATE,'iw') from dual;
Truncate to 00:00:00 (12:00:00am) of the first day of the month for the input date.
SELECT TRUNC(SYSDATE,'mm') from dual;
Truncate to 00:00:00 (12:00:00am) of the first day of the year for the input date. I usually use YYYY as my truncation format to be consistent with my conversion formats for years, but TRUNC will support 1,2,3,or 4 Y-characters as the truncation format. The RR and RRRR formats are also supported
SELECT TRUNC(SYSDATE,'yyyy') from dual; SELECT TRUNC(SYSDATE,'yyy') from dual; SELECT TRUNC(SYSDATE,'yy') from dual; SELECT TRUNC(SYSDATE,'y') from dual; SELECT TRUNC(SYSDATE,'rr') from dual; SELECT TRUNC(SYSDATE,'rrrr') from dual;
Truncate to 00:00:00 (12:00:00am) of the first day of the century for the input date.
SELECT TRUNC(SYSDATE,'cc') from dual;
Truncate to 00:00:00 (12:00:00am) of the first day of the quarter for the input date.
SELECT TRUNC(SYSDATE,'q') from dual;
Important Note about truncated values
Many data querying tools will show truncated date values without the time component. Thus “2021-05-15 00:00:00”, might be displayed as “2021-05-15”. This behavior has given rise to a common misconception that TRUNC will remove the time portion of a date value, but it does not. A date value always has year, month, day, hours, minutes, seconds in it – even if they aren’t all displayed.
NEXT_DAY will look forward in the calendar for the next date of the given name, using the session’s current NLS_DATE_LANGUAGE. The function does not include the input day itself as part of the search forward. So, if you input a Monday and search for Monday, it will return the following week. Also note, while the year/month/day portion of the result will change from the input value, the hours/minutes/seconds of the input will be preserved. The search day can be a fully spelled out name or a valid abbreviation for the current language. The return type is always a DATE.
SELECT NEXT_DAY(SYSDATE,'Monday') from dual;
LAST_DAY will return the last day of the month for the input value. Like NEXT_DAY, the hours/minutes/seconds of the input value will be preserved in the output DATE value.
SELECT LAST_DAY(SYSDATE) from dual;
The ADD_MONTHS function will, as the name implies, add an integer number of months (positive, negative, or zero) to an input value. As with NEXT_DAY and LAST_DAY, the hours/minutes/seconds of the input value will be preserved. Since months do not have an equal number of days, if the resulting date will be greater than the number of days in the month the result will be changed to the last day of the month. Also, if the input day is the last day of the month, the result will always be shifted to the last day of the month, even if that means the resulting day number will change. It will even recognize leap years and adjust to February 28 or 29 appropriately.
SELECT ADD_MONTHS(SYSDATE, -1), ADD_MONTHS(SYSDATE, 1)FROM DUAL; SELECT ADD_MONTHS(date '2021-03-29', -1) FROM DUAL; SELECT ADD_MONTHS(date '2021-03-30', -1) FROM DUAL; SELECT ADD_MONTHS(date '2021-03-31', -1) FROM DUAL; SELECT ADD_MONTHS(date '2021-02-28', -1) FROM DUAL; SELECT ADD_MONTHS(date '2021-02-28', 1) FROM DUAL; SELECT ADD_MONTHS(date '2021-02-28', -12) FROM DUAL;
Date Math and Other Tricks
If you know how to use all of the basic functions shown above you can combine those functions along with simple date and timestamp math to find or generate a variety of values and functionality.
Find various U.S. holidays for a given year. There are, of course, other methods beyond those shown below. Halloween could be found by using LAST_DAY of the intermediate October 1 result from adding 9 months instead of subtracting 1 from the November 1 result of adding 10 months. Note these calculations are for the date rules in effect as of this writing. If you are using older years some holidays were observed at other dates or not at all. In a previous article I provided code to generate holidays observing U.S. federal holiday rules across various legal changes through the years.
select 'New Year''s Day' Day_Name, 'January 1' description, trunc(sysdate,'yyyy') date_result from dual union all select 'Birthday of Martin Luther King, Jr.', 'Third Monday in January', next_day(trunc(sysdate,'yyyy')-1,'Monday')+14 from dual union all select 'Independence Day' , 'July 4' , add_months(trunc(sysdate,'yyyy'),6)+3 from dual union all select 'Labor Day' , 'First Monday in September' , next_day(add_months(trunc(sysdate,'yyyy'),8)-1,'Monday') from dual union all select 'Halloween' , 'October 31' , add_months(trunc(sysdate,'yyyy'),10)-1 from dual union all select 'Veterans Day (formerly Armistice Day)' , 'November 11' , add_months(trunc(sysdate,'yyyy'),10)+10 from dual union all select 'Thanksgiving' , 'Fourth Thursday in November' , next_day(add_months(trunc(sysdate,'yyyy'),10)-1,'Thursday')+21 from dual union all select 'Christmas' , 'December 25' , add_months(trunc(sysdate,'yyyy'),11)+24 from dual union all select 'New Year''s Eve' , 'December 31' , add_months(trunc(sysdate,'yyyy'),12)-1 from dual;
Determine if a given year is a leap year. You can do the math of checking for divisibility by 4, excluding divisible by 100, except when divisible by 400; but simpler is to check for the existence of February 29 or if the last day of the year is number 366. This does assume the input date will be within the Gregorian calendar.
--- Does February 29 exist in that year?
SELECT CASE
WHEN EXTRACT(DAY FROM TRUNC(SYSDATE, 'yyyy') + 59) = 29
THEN
'Leap Year'
ELSE
'not a leap year'
END
FROM DUAL;
--- Is December 31 the 366th day of the year?
SELECT CASE
WHEN TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), 12) - 1, 'ddd') =
'366'
THEN
'Leap Year'
ELSE
'not a leap year'
END
FROM DUAL;
-- Is the year divisible by 4, but not 100, unless by 400?
SELECT CASE
WHEN (MOD(yyyy, 4) = 0 AND MOD(yyyy, 100) != 0)
OR MOD(yyyy, 400) = 0
THEN
'Leap Year'
ELSE
'not a leap year'
END
FROM (SELECT EXTRACT(YEAR FROM SYSDATE) yyyy FROM DUAL);
The ROUND function works with DATE and TIMESTAMPS, similar to TRUNC, always returning a DATE value. Sometimes though, you may want to round to non-standard values, such as to 15-minute quarters of the hour (00, 15, 30, 45 minutes past the hour.)
-- Round DOWN to the nearest quarter hour
SELECT TRUNC(SYSDATE, 'hh24')
+ CEIL((TO_NUMBER(TO_CHAR(SYSDATE, 'mi'), '99') - 14) / 15) * 15 / 1440
FROM DUAL;
-- Round UP to the nearest quarter hour
SELECT TRUNC(SYSDATE, 'hh24')
+ FLOOR((TO_NUMBER(TO_CHAR(SYSDATE, 'mi'), '99') + 15) / 15) * 15 / 1440
FROM DUAL;
Sometimes it’s useful to have an extreme value that you know is outside of normal bounds. The earliest date supported by Oracle is Julian day 1. By default the sign of a negative date isn’t displayed, so I’m formatting it explicitly with the “s” mask.
select to_char(to_date('1','j'),'syyyy-mm-dd hh24:mi:ss')
from dual;
select to_char(to_timestamp('1','j'),'syyyy-mm-dd hh24:mi:ssxff')
from dual;
At the other end of the Oracle calendar December 31, 9999 23:59:59 for date values and December 31, 9999 23:59:59.999999999 for timestamps marks the “end of time”.
select to_date('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
from dual;
select to_timestamp('9999-12-31 23:59:59.999999999','yyyy-mm-dd hh24:mi:ssxff')
from dual;
Generate a date for every day of a given month.
SELECT TRUNC(SYSDATE, 'mm') + LEVEL - 1 FROM DUAL CONNECT BY TRUNC(SYSDATE, 'mm') + LEVEL - 1 <= LAST_DAY(SYSDATE);
Generate the first day of every month for a year.
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1)
FROM DUAL
CONNECT BY LEVEL <= 12;
Generate the last day of every month for a year.
SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1))
FROM DUAL
CONNECT BY LEVEL <= 12;
I hope to keep this article as a living document updating it with other one-liners and quick examples. Keeping it distinct from deeper dives into specific functionality.
Thank you for reading, I always welcome comments; but in this case I especially welcome ideas or requests for additional examples to help it grow into a more useful resource.