First question first : Why do you need to convert date in a format with function ?
Reason is simple and that is standardizing the date format in entire application. That enables accurate calculation at end or final result.
In real world, not every upstream system sends you a single date format and you see a real need to have a standard format for a date throughout your system. In order to standardize date format, you should be reading the input coming from file as a string and associate the data with respective date elements to transform it to a standard form. Oracle TO_DATE () is a power function that equip you to convert characters (any character* datatype) to a date.
Julian date* is an exception, the input should be an integer, but not a character. You can check Julian date example below.
Oracle TO_DATE () Function Syntax
One real time issue or example i can narrate now ( See screen shot carefully )
- Every database is configured to store date data on a particular territory and language setting.
- Now imagine if your database has standard of American language and one of your source system is sending French name of the date data
- You can handle it using Oracle TO_DATE () function. It will allow you to override those settings and convert the character to date per your language settings.
- In this example “NLS_DATE_LANGUAGE” is used to read the string to date.
Different date elements and oracle to_date () function Usage:
Hummm Oracle provides a wide range of date elements that support to_date() function to use effectively. Below are the different elements and combinations those can be used to read the date data accurately.
|Element||Description||Sample to_date() SQL||Result|
|D||Day of week (1-7).|
|DAY||Name of day|
SELECT TO_DATE(‘July 18, 1989, 11:00 A.M.’, ‘Month dd, YYYY, HH:MI A.M.’),
|DD||Day of month (1-31).|
|DDD||Day of year (1-366).|
|YYYY||4-digit year; S prefixes BC dates with a minus sign.|
|Last 3, 2, or 1 digit(s) of year.|
|HH||Hour of day (1-12).|
|HH24||Hour of day (0-23).|
|J||Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.||select to_date (209,’J’) from dual;||28-Jul-13|
|MM||Month (01-12; January = 01).||select to_date(’18 07 2013′, ‘DD MM YYYY’), to_date(’19 Jul 2013′, ‘DD MON YYYY’),|
to_date(’20 July 2013′, ‘DD MONTH YYYY’) from dual;
|MON||Abbreviated name of month.|
|MONTH||Full Name of month|
|RRRR||Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.||SELECT TO_CHAR(TO_DATE(’27-OCT-92′, ‘DD-MON-RR’) ,’YYYY’),|
TO_CHAR(TO_DATE(’27-OCT-14′, ‘DD-MON-RR’) ,’YYYY’)
|SS||Second (0-59).||select to_date(‘2013,199,00:00:00’, ‘YYYY,DDD,HH24:MI:SS’) from dual;||18-Jul-13|
|SSSSS||Seconds past midnight (0-86399).||select to_date(‘2013,199,20152’, ‘YYYY,DDD,SSSSS’) from dual;||18-Jul-13|
I would glad to hear you back with inputs which can add help to Oracle to_date learning !!!!!!