In Oracle this is done through a function called TO_DATE, which converts a string to a DATE value, again according to the DATE format, for example.
insert into x
values(99, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
The general usage of TO_DATE is:
TO_DATE(<string>, '<format>')
where the <format> string has the following options:
| MM |
Numeric month (e.g., 07) |
| MON |
Abbreviated month name (e.g., JUL) |
| MONTH |
Full month name (e.g., JULY) |
| DD |
Day of month (e.g., 24) |
| DY |
Abbreviated name of day (e.g., FRI) |
| YYYY |
4-digit year (e.g., 1998) |
| YY |
Last 2 digits of the year (e.g., 98) |
| RR |
Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906 |
| AM (or PM) |
Meridian indicator |
| HH |
Hour of day (1-12) |
| HH24 |
Hour of day (0-23) |
| MI |
Minute (0-59) |
| SS |
Second (0-59) |
Finally, you can change the default DATE format of Oracle from "DD-MON-YY" to something you like by issuing the following command in sqlplus:
alter session set NLS_DATE_FORMAT='<my_format>';
The change is only valid for the current sqlplus session. |
