Dealing with Dates (Part 1)

---

"I suggest a new strategy, R2. Let the Wookiee win" - C-3PO, Star Wars Episode IV: A New Hope

---

Doesn't matter what the DB is that you're working on, working with Temporal data types is a royal pain in the rear end.

I can see this being an exceptionally long post, so I think I'm going to break it up into multiple parts. Let's start with Oracle today and how to transform Date data types into Text for display and the reverse, taking a text representation of a temporal value and placing it into a Date data type.

Oracle provides a long list of built in functions to deal with the conversion of data from one data type to another. You can see them in this list in the last column - they all start with "to_".

Oracle has two data types dealing with date and time. The first is simply DATE: a representation of a date and time to the second. The second is TIMESTAMP: a representation of date and time to fractions of a second. The default has a precision of 6 decimal places, but can go as high as 9. (There is a third option, TIMESTAMP WITH {LOCAL} TIMEZONE which is a timestamp with a built in time zone offset, but for the sake of what we're talking about here, this is just a TIMESTAMP).

So, this gives us three functions to play with:

• to_date
• to_timestamp (there's also to_timestamp_tz, but I'm not touching that here)
• to_char

All three functions take a formatted string and converts it to a date or timestamp or character representation. The format of the function is:

function(string1, [format_string], [nls_parameter])

The format_string is optional. If it is not specified, the string needs to be in the default date format as defined implicitly by the NLS_TERRITORY initialization parameter or explicitly set by the NLS_DATE_FORMAT parameter. Best practice dictates that you always specify the format string. The length of the format string may not exceed 22 characters. Capitalization of a Format Element carries over to the output. A Format Element on "DAY" would output "MONDAY", where as a Format Element of "Day" would output "Monday".

The nls_parameter specifies the language the date is bring formatted to. It must be of the format 'NLS_DATE_LANGUAGE = language'

The to_char function can also be used to convert numbers to character or hex representations of ASCII codes to characters, but we're only talking about dates here.

The format string parameter can be any of the following values: (HTML for this table from Oracle Documentation, see references link at end of document)

Element	Description
/ , . ; : "text"	Punctuation and quoted text is reproduced in the result.
AD A.D.	AD indicator with or without periods.
AM A.M.	Meridian indicator with or without periods.
BC B.C.	BC indicator with or without periods.
CC SCC	Century. If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year. For example, 2002 returns 21; 2000 returns 20.
D	Day of week (1-7). This element depends on the NLS territory of the session.
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DL	Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format 'fmDay, dd. Month yyyy'. Restriction: You can specify this format only with the TS element, separated by white space.
DS	Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR'. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format 'DD/MM/RRRR'. Restriction: You can specify this format only with the TS element, separated by white space.
DY	Abbreviated name of day.
E	Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE	Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]	Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in DATE formats. Examples: 'HH:MI:SS.FF' SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM	Returns a value with no leading or trailing blanks.
FX	Requires exact matching between the character data and the format model.
HH HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
IW	Week of year (1-52 or 1-53) based on the ISO standard.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard.
J	Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI	Minute (0-59).
MM	Month (01-12; January = 01).
MON	Abbreviated name of month.
MONTH	Name of month.
PM P.M.	Meridian indicator with or without periods.
Q	Quarter of year (1, 2, 3, 4; January - March = 1).
RM	Roman numeral month (I-XII; January = I).
RR	Lets you store 20th century dates in the 21st century using only two digits.
RRRR	Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
TS	Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters. Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD	Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats. Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH	Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats. Example: 'HH:MI:SS.FFTZH:TZM'.
TZM	Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats. Example: 'HH:MI:SS.FFTZH:TZM'.
TZR	Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats. Example: US/Pacific
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.
X	Local radix character. Example: 'HH:MI:SSXFF'.
Y,YYY	Year with comma in this position.
YEAR SYEAR	Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY SYYYY	4-digit year; S prefixes BC dates with a minus sign.
YYY YY Y	Last 3, 2, or 1 digit(s) of year.

All of this is great, what about examples?

select to_char(sysdate, 'Day, DD Month, YYYY "@" HH24:MI:SS') from DUAL;

returns an error because the format string is over 22 characters. The elements are all valid, but combined they are too long.

select to_char(sysdate, "Day, DD Month, YYYY @ HH24:MI:SS") from DUAL;

returns

Tuesday, 27 September, 2011

Next: The SQL Server CONVERT function

References:
Oracle 11g SQL Language Reference: to_date function
Oracle 11g SQL Language Reference: Format Models