The TO_DATETIME function interprets a character string based on the date-time format given as an argument, converts it to a DATETIME type value, and returns it. For the format, see TO_CHAR Function (date_time). If format is not specified, the "HH:MI:SS.FF [am|pm] MM/DD/YYYY" format is applied by default.
TO_DATETIME(string_argument[,format_argument[,date_lang_string_literal]])
string_argument :
• character strings
• NULL
format_argument :
• character strings (see the table Date/Time Format 1)
• NULL
date_lang_string_literal : (see the table Example of date_lang_string_literal)
• 'en_US'
• 'ko_KR'
--selecting a datetime type value casted from a string in the specified format
SELECT TO_DATETIME('13:10:30 12/25/2008');
to_datetime('13:10:30 12/25/2008')
=====================================
01:10:30.000 PM 12/25/2008
SELECT TO_DATETIME('08-Dec-25 13:10:30.999', 'YY-Mon-DD HH24:MI:SS.FF');
to_datetime('08-Dec-25 13:10:30.999', 'YY-Mon-DD HH24:MI:SS.FF', 'en_US')
=====================================
01:10:30.999 PM 12/25/2008
SELECT TO_DATETIME('DATE: 12-25-2008 TIME: 13:10:30.999', '"DATE:" MM-DD-YYYY "TIME:" HH24:MI:SS.FF');
to_datetime('DATE: 12-25-2008 TIME: 13:10:30.999', '"DATE:" MM-DD-YYYY "TIME:" HH24:MI:SS.FF', 'en_US')
=====================================
01:10:30.999 PM 12/25/2008