Oracle Database Dates 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.)
