Oracle Database Generating Dates with No Time Component


Example

All DATEs have a time component; however, it is customary to store dates which do not need to include time information with the hours/minutes/seconds set to zero (i.e. midnight).

Use an ANSI DATE literal (using ISO 8601 Date format):

SELECT DATE '2000-01-01' FROM DUAL;

Convert it from a string literal using TO_DATE():

SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;

(More information on the date format models can be found in the Oracle documentation.)

or:

SELECT TO_DATE(
         'January 1, 2000, 00:00 A.M.',
         'Month dd, YYYY, HH12:MI A.M.',
         'NLS_DATE_LANGUAGE = American'
       )
FROM   DUAL;

(If you are converting language specific terms such as month names then it is good practice to include the 3rd nlsparam parameter to the TO_DATE() function and specify the language to be expected.)