Oracle uses the date format to store both date and time in a single combined column that includes month, day, year, hour, minute, second and millisecond. Dates only compare to other dates successfully. This rule makes it necessary to convert strings within sequential files to the date format before comparing them to a date stored in an Oracle table. Convert the string to a date with a simple structured query language (SQL) command.
- Skill level:
Other People Are Reading
Things you need
- Oracle database
- Interactive query tool
Read a date value from an input file. For this example, assume that the value is "20100101".
Use the "to_date" function to convert the string to a date. The format is
to_date(string 1, [format_mask]).
String1 is the string value, and format_mask defines the output format.
In this example,
Oracle uses the to_date function with the update and insert commands without requiring the user to explicitly coding the statement. The two statements shown below function exactly the same. The first one is an example of implicit coding, and the second is an example of explicit coding.
insert into date_table values ('date 1', '20100101');
insert into date_table values ('date 2', to_date('20100101', 'yyyymmdd');
Tips and warnings
- The format mask allows the user to define the date format in many different configurations.
- MM stands for numeric month.
- MON stands for the abbreviated month name.
- MONTH stands for the full month name.
- DD stands for the day of the month.
- DY stands for the abbreviated day name.
- YYYY stands for the four digit year.
- YY stands for the two digit year.
- HH stands for hour of the day.
- HH24 stands for the military hour of the day.
- MI stands for the minute.
- SS stands for the second.
- AM or PM stands for the Meridian indicator.
- If the string value is not a valid date, the to_date function returns an error message when the SQL statement is executed.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for