alter session set NLS_DATE_LANGUAGE = 'AMERICAN' ; alter session set NLS_DATE_FORMAT = 'SYYYY-MM-DD' ; alter session set NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' ; alter session set NLS_TIMESTAMP_TZ_FORMAT = 'SYYYY-MM-DD HH24:MI:SS TZH:TZM' ; create table t ( key integer , c_date date , c_timestamp timestamp (9) , c_timestamp_with_time_zone timestamp (9) with time zone , c_timestamp_with_local_time_zo timestamp (9) with local time zone , c_time_with_time_zone timestamp (9) with time zone , c_interval_year_to_month interval year to month , c_interval_day_to_second interval day to second ) ; insert into t values ( -3, date '-2400-10-31' , timestamp '-2400-10-31 12:34:56' , timestamp '-2400-10-31 12:34:56 +03:00' , timestamp '-2400-10-31 12:34:56' , timestamp '2000-01-01 12:34:56 +03:00' , null , null ); insert into t values ( -2, date '-500-02-28' , timestamp '-500-02-28 00:00:00' , timestamp '-500-02-28 00:00:00 +05:30' , timestamp '-500-02-28 00:00:00' , timestamp '2000-01-01 00:00:00 +05:30' , null , null ); insert into t values ( -1, date '-1-12-31' , timestamp '-1-12-31 12:00:00' , timestamp '-1-12-31 12:00:00 Europe/Tallinn' , timestamp '-1-12-31 12:00:00' , timestamp '2000-01-01 12:00:00 Europe/Tallinn' , null , null ); insert into t values ( 0, date '0000-01-01' , timestamp '0000-01-01 12:00:00' , timestamp '0000-01-01 12:00:00 +00:00' , timestamp '0000-01-01 12:00:00' , timestamp '2000-01-01 12:00:00 +00:00' , null , null ); insert into t values ( 1, date '1-06-21' , timestamp '1-06-21 22:00:00' , timestamp '1-06-21 22:00:00 -12:00' , timestamp '1-06-21 22:00:00' , timestamp '2000-01-01 22:00:00 -12:00' , null , null ); insert into t values ( 2, date '0820-11-23' , timestamp '0820-11-23 11:23:58' , timestamp '0820-11-23 11:23:58 Asia/Tehran' , timestamp '0820-11-23 11:23:58' , timestamp '2000-01-01 11:23:58 Asia/Tehran' , null , null ); insert into t values ( 3, date '1947-12-16' , timestamp '1947-12-16 01:10:11.101010101' , timestamp '1947-12-16 01:10:11.101010101 America/New_York' , timestamp '1947-12-16 01:10:11.101010101' , timestamp '2000-01-01 01:10:11.101010101 America/New_York' , null , null ); insert into t values ( 4, date '2007-06-15' , timestamp '2007-06-15 14:30:00.123456789' , timestamp '2007-06-15 14:30:00.123456789 Canada/Yukon' , timestamp '2007-06-15 14:30:00.123456789' , timestamp '2000-06-01 14:30:00.123456789 Canada/Yukon' , null , null ); commit; create table datetime_formats ( format_group varchar2(20) , format_element varchar2(14) , note varchar2(1000) ); insert into datetime_formats values( 'Date', 'DL', 'Date - long format' ); insert into datetime_formats values( 'Date', 'DS', 'Date - short format' ); insert into datetime_formats values( 'Epoch', 'AD' , 'returns either AD or BC' ); insert into datetime_formats values( 'Epoch', 'BC' , 'returns either AD or BC' ); insert into datetime_formats values( 'Epoch', 'A.D.', 'returns either A.D. or B.C.' ); insert into datetime_formats values( 'Epoch', 'B.C.', 'returns either A.D. or B.C.' ); insert into datetime_formats values( 'Epoch', 'E' , 'era name - abbrev. (Japanese Imperial, ROC Official, and Thai Buddha calendars)' ); insert into datetime_formats values( 'Epoch', 'EE' , 'era name - full (Japanese Imperial, ROC Official, and Thai Buddha calendars)' ); insert into datetime_formats values( 'Century', 'CC', 'century number' ); insert into datetime_formats values( 'Century', 'SCC','century number; BC dates start with minus sign ("-"), AD dates start with space (" ")' ); insert into datetime_formats values( 'Year', 'YEAR' , 'year, spelled out, uppercase' ); insert into datetime_formats values( 'Year', 'Year' , 'year, spelled out, initcap' ); insert into datetime_formats values( 'Year', 'year' , 'year, spelled out, lowercase' ); insert into datetime_formats values( 'Year', 'SYEAR', 'year, spelled out, uppercase; BC dates indicated with a minus sign' ); insert into datetime_formats values( 'Year', 'SYear', 'year, spelled out, initcap; BC dates indicated with a minus sign' ); insert into datetime_formats values( 'Year', 'syear', 'year, spelled out, lowercase; BC dates indicated with a minus sign' ); insert into datetime_formats values( 'Year', 'Y,YYY', 'year - with comma' ); insert into datetime_formats values( 'Year', 'YYYY' , 'year - all four digits' ); insert into datetime_formats values( 'Year', 'YYY' , 'year - last three digits' ); insert into datetime_formats values( 'Year', 'YY' , 'year - last two digits' ); insert into datetime_formats values( 'Year', 'Y' , 'year - last digit' ); insert into datetime_formats values( 'Year', 'SYYYY', 'year - all four digits; BC dates indicated with a minus sign' ); insert into datetime_formats values( 'Year', 'IYYY' , 'year - all four digits (ISO year)' ); insert into datetime_formats values( 'Year', 'IYY' , 'year - last three digits (ISO year)' ); insert into datetime_formats values( 'Year', 'IY' , 'year - last two digits (ISO year)' ); insert into datetime_formats values( 'Year', 'I' , 'year - last digit (ISO year)' ); insert into datetime_formats values( 'Year', 'RR' , 'year - last two digits' ); insert into datetime_formats values( 'Year', 'RRRR' , 'year - all four digits' ); insert into datetime_formats values( 'Quarter', 'Q', 'quarter - 1-4 (Jan-Mar = 1)' ); insert into datetime_formats values( 'Month', 'MM' , 'month number - 01-12 (Jan = 01)' ); insert into datetime_formats values( 'Month', 'RM' , 'month number - I-XII (in Roman numerals)' ); insert into datetime_formats values( 'Month', 'MON' , 'month name - abbreviated' ); insert into datetime_formats values( 'Month', 'MONTH', 'month name - full' ); insert into datetime_formats values( 'Week', 'W' , 'week number of month - 1-5 (week 1 runs from day 1 to day 7 of month)' ); insert into datetime_formats values( 'Week', 'IW', 'week number of year - 1-52 or 1-53 (based on ISO standard)' ); insert into datetime_formats values( 'Week', 'WW', 'week number of year - 1-53 (week 1 runs from Jan. 1 to Jan. 7)' ); insert into datetime_formats values( 'Day', 'D' , 'day number of week - 1-7 (starting day of week depends on NLS_TERRITORY)' ); insert into datetime_formats values( 'Day', 'DD' , 'day number of month - 1-31' ); insert into datetime_formats values( 'Day', 'DDD', 'day number of year - 1-366' ); insert into datetime_formats values( 'Day', 'J' , 'day number since January 1, 4712 BC (Julian day)' ); insert into datetime_formats values( 'Day', 'DAY', 'day name - full' ); insert into datetime_formats values( 'Day', 'DY' , 'day name - abbreviated' ); insert into datetime_formats values ( 'Time', 'TS', 'Time - short format' ); insert into datetime_formats values ( 'Meridian', 'AM' , 'meridian indicator' ); insert into datetime_formats values ( 'Meridian', 'A.M.', 'meridian indicator' ); insert into datetime_formats values ( 'Meridian', 'PM' , 'meridian indicator' ); insert into datetime_formats values ( 'Meridian', 'P.M.', 'meridian indicator' ); insert into datetime_formats values ( 'Hour', 'HH' , 'hour of day (1-12)' ); insert into datetime_formats values ( 'Hour', 'HH12', 'hour of day (1-12)' ); insert into datetime_formats values ( 'Hour', 'HH24', 'hour of day (0-23)' ); insert into datetime_formats values ( 'Minute', 'MI', 'minute of hour (0-59)' ); insert into datetime_formats values ( 'Second', 'SS' , 'second of minute (0-59)' ); insert into datetime_formats values ( 'Second', 'SSSSS', 'seconds past midnight (0-86399)' ); insert into datetime_formats values ( 'Subsecond', 'FF1', 'fractional seconds (1 digit precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF2', 'fractional seconds (2 digits precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF3', 'fractional seconds (3 digits precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF4', 'fractional seconds (4 digits precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF5', 'fractional seconds (5 digits precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF6', 'fractional seconds (6 digits precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF7', 'fractional seconds (7 digits precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF8', 'fractional seconds (8 digits precision)' ); insert into datetime_formats values ( 'Subsecond', 'FF9', 'fractional seconds (9 digits precision)' ); insert into datetime_formats values ( 'TZ', 'TZH', 'time zone hour' ); insert into datetime_formats values ( 'TZ', 'TZM', 'time zone minute' ); insert into datetime_formats values ( 'TZ', 'TZR', 'time zone region' ); insert into datetime_formats values ( 'TZ', 'TZD', 'daylight savings time indicator' ); commit;