This tutorial explains how to use Oracle TO_DATE function with basic syntax and examples.
TO_DATE function convert a character string to a date format.
Syntax:
TO_DATE(char[, 'format_model'], [nlsparams])
Char : The character that will be converted to a date
'format_model' : format that will be used to convert char to a date. If format_model is not specified, the format is DD-MON-YY. and It can be one or a combination of the following values:
Parameter
Explanation
SCC or CC
Century; server prefixes BC date with –
Years in dates YYYY or SYYYY
Year; server prefixes BC date with –
YYY or YY or Y
Last three, two, or one digits of year
Y, YYY
Year with comma in this position
IYYY, IYY, IY, I
Four-, three-, two-, or one-digit year based on the ISO standard
SYEAR or YEAR
Year spelled out; server prefixes BC date with –
Q
Quarter of year
MM
Month: two-digit value
MONTH
Name of month padded with blanks to length of nine characters
MON
Name of month, three-letter abbreviation
RM
Roman or brown
WW or W
Week of year or month
DDD or DD or D
Day of year, month, or week
DAY
Name of day padded with blanks to a length of nine characters
DY
Name of day; three-letter abbreviation
J
Julian day; the number of days since December 31, 4713 BC
RRRR
Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
HH
Hour of day (1-12).
HH12
Hour of day (1-12).
HH24
Hour of day (0-23).
MI
Minute (0-59).
SS
Second (0-59).
SSSSS
Seconds past midnight (0-86399).
AM, AM, PM, or PM
Meridian indicator
Nlsparams : has the same purpose in this function as in the TO_CHAR function for date conversion
Example:
Let's get some examples about Oracle TO_DATE and how to use it:
