SQL Snippets ™: Tutorials for Oracle Developers

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;