|
Tip of the Week
(Archived)
Dear Oracle...When is Thanksgiving?
We know the answer to that, the 4th Thursday of November. But does Oracle know?
And of course the answer is yes. I just need to phrase the question correctly, via a SQL query, like this:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
COLUMN thanksgiving FORMAT A12 HEADING "Thanksgiving"
SELECT NEXT_DAY(TO_DATE('21-NOV-' || TO_CHAR(SYSDATE, 'YYYY')), 'THU') thanksgiving
FROM DUAL;
Thanksgiving
------------
24-NOV-2005
The ALTER SESSION command changes the date format to include a 4 digit year, and the COLUMN command sets the column width and heading. But the real work is done in the SELECT statement, as follows:
- TO_DATE('21-NOV-' || TO_CHAR(SYSDATE,
'YYYY')) - Begin with November 21 of the current year. The earliest
date possible for Thanksgiving is November 22. So the first Thursday
after November 21 will be Thanksgiving.
-
NEXT_DAY(TO_DATE('21-NOV-' || TO_CHAR(SYSDATE, 'YYYY')), 'THU')
- The NEXT_DAY function returns the date of the first Thursday after
November 21, which will be Thanksgiving. If November 21 happens to be a Thursday, then the date returned will
be November 28, the latest date possible for Thanksgiving.
Happy Thanksgiving!
Note: This tip was tested on Oracle9i.
Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click
here for the tip feedback page. Thank you. |
