Oracle/PLSQL: to_date Function

To insert a date/time value into the Oracle table, you'll need to use the to_date function. The to_date function allows you to define the format of the date/time value.

For example, we cocould Insert the '3-may-03 21:02:44 'value as follows:

Insert into table_name
(Date_field)
Values
(To_date ('2014/1/03 21:02:44 ', 'yyyy/MM/DD hh24: MI: ss '));

 

Insert into mytable (firstcol, event_timestamp) values ('test1', to_date ('2016/5/22 12:00:00 am ','Mm/DD/YYYY hh: MI: SS AM'));

In Oracle/PLSQL,To_dateFunction converts a string to a date.

The syntax forTo_dateFunction is:

To_date (string1, [format_mask], [nls_language])

String1Is the string that will be converted to a date.

Format_maskIs optional. This is the format that will be used to convertString1To a date.

Nls_languageIs optional. This is the NLS language used to convertString1To a date.

 

The following is a list of options forFormat_maskParameter. These parameters can be used in specified combinations.

Parameter	Explanation
Year	Year, spelled out
Yyyy	4-digit year
Yyy YY Y	Last 3, 2, or 1 digit (s) of year.
Iyy Iy I	Last 3, 2, or 1 digit (s) of ISO year.
Iyyy	4-digit year based on the ISO standard
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.
Q	Quarter of year (1, 2, 3, 4; Jan-MAR = 1 ).
Mm	Month (01-12; Jan = 01 ).
Mon	Abbreviated name of month.
Month	Name of month, padded with blanks to length of 9 characters.
Rm	Roman numeral month (I-XII; Jan = I ).
WW	Week of year (1-53) Where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7 ).
Day	Name of day.
Dd	Day of month (1-31 ).
Ddd	Day of year (1-366 ).
Dy	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
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 ).
FF	Fractional seconds. Use a value from 1 to 9 after ff to indicate the number of digits in the fractional seconds. For example, 'ff4 '.
Am, A. M., PM, or P. M.	Meridian indicator
Ad or A.D	Ad indicator
BC or B .C.	BC indicator
TZD	Daylight Savings information. For example, 'pst'
Tzh	Time zone hour.
Tzm	Time zone minute.
Tzr	Time Zone region.

 

Applies:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

 

For example:

To_date ('2014/1/09', 'yyyy/MM/dd ')	Wocould return a date value of July 9, 2003.
To_date ('20140901', 'mmddyy ')	Wocould return a date value of July 9, 2003.
To_date ('20140901', 'yyyymmdd ')	Wocould return a date value of MAR 15,200 2.