TO_DATE

The TO_DATE function formats a date string according to a formatting specification, and returns a  DATE values do not store time components.

Syntax

TO_DATE( dateStrExpr, formatStr );

dateStrExpr

A string expression that contains a date that is formatted according to the format string.

formatStr

A string that specifies the format you want applied to the dateStr. See the Date and Time Formats section below for more information about format specification.

Results

The result is always a  DATE value.

Date and Time Formats

Splice Machine supports date and time format specifications based on the Java SimpleDateFormat class.

Date and time value formats are used for both parsing input values and for formatting output values. For example, the format specification yyyy-MM-dd HH:mm:ssZ parses or formats values like 2014-03-02 11:47:44-0800.

The remainder of this topic describes format specifications in these sections:

  • Pattern Specifications contains a table showing details for all of the pattern letters you can use.
  • Presentation Types describes how certain pattern letters are interpreted for parsing and/or formatting.
  • Examples contains a number of examples that will help you understand how to use formats.

Pattern Specifications

You can specify formatting or parsing patterns for date-time values using the pattern letters shown in the following table. Note that pattern letters are typically repeated in a format specification. For example, YYYY or YY. Refer to the next section for specific information about multiple pattern letters in the different presentation types.

Pattern Letter Meaning Presentation Type Example
G Era designator Text BC
y Year Year 2015 -or- 15
Y Week year Year 2011 -or- 11
M Month in year Month July -or- Jul -or- 07
w Week in year Number 27
W Week in month Number 3
D Day in year Number

212

A common usage error is to mistakenly specify DD for the day field:

  • use dd to specify day of month
  • use DD to specify the day of the year
d Day in month Number

13

F Day of week in month Number 2
E Day name in week Text Tuesday -or- Tue
u Day number of week
(1=Monday, 7=Sunday)
Number 4
a AM / PM marker Text PM
H Hour in day
(0 - 23)
Number 23
k Hour in day
(1 - 24)
Number 24
K Hour in AM/PM
(0 - 11)
Number 11
h Hour in AM/PM
(1 - 12)
Number 12
m Minute in hour Number 33
s Second in minute Number 55
S Millisecond Number 959
z Time zone General time zone Pacific Standard Time -or- PST -or- GMT-08:00
Z Time zone RFC 822 time zone -0800
X Time zone ISO 8601 time zone -08 -or- -0800 -or- -08:00
' Escape char for text Delimiter  
'' Single quote Literal '

Presentation Types

How a presentation type is interpreted for certain pattern letters depends on the number of repeated letters in the pattern. In some cases, as noted in the following table, other factors can influence how the pattern is interpreted.

Presentation Type Description
Text

For formatting, if the number of pattern letters is 4 or more, the full form is used. Otherwise, a short or abbreviated form is used, if available.

For parsing, both forms are accepted, independent of the number of pattern letters.

Number

For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount.

For parsing, the number of pattern letters is ignored unless it's needed to separate two adjacent fields.

Year
(for Gregorian calendar)

For formatting, if the number of pattern letters is 2, the year is truncated to 2 digits; otherwise it is interpreted as a number.

For parsing, if the number of pattern letters is more than 2, the year is interpreted literally, regardless of the number of digits; e.g.:

  • if you use the pattern MM/dd/yyyy, the value 01/11/12 parses to January 11, 12 A.D.
  • if you use the pattern MM/dd/yy, the value 01/11/12 parses to January 11, 2012.

If the number of pattern letters is one or two, (y or yy), the abbreviated year is interpreted as relative to a century; this is done by adjusting dates to be within 80 years before and 20 years after the current date.

Year
(other calendar systems)

Calendar-system specific forms are applied.

For both formatting and parsing, if the number of pattern letters is 4 or more, a calendar specific long form is used. Otherwise, a calendar specific short or abbreviated form is used.

Month If the number of pattern letters is 3 or more, the month is interpreted as text; otherwise, it is interpreted as a number.
General time zone

Time zones are interpreted as text if they have names.

For time zones representing a GMT offset value, the following syntax is used:

GMT Sign Hours : Minutes

where:

Sign is + or -

Hours is either Digit or Digit Digit, between 0 and 23.

Minutes is Digit Digit and must be between 00 and 59.

For parsing, RFC 822 time zones are also accepted.

RFC 822 time zone

For formatting, use the RFC 822 4-digit time zone format is used:

Sign TwoDigitHours Minutes

TwoDigitHours must be between 00 and 23.

For parsing General time zones are also accepted.

ISO 8601 time zone

The number of pattern letters designates the format for both formatting and parsing as follows:

  Sign TwoDigitHours Z
| Sign TwoDigitHours Minutes Z
| Sign TwoDigitHours : Minutes Z

For formatting:

  • if the offset value from GMT is 0, Z value is produced
  • if the number of pattern letters is 1, any fraction of an hour is ignored

For parsing, Z is parsed as the UTC time zone designator. Note that General time zones are not accepted.

Delimiter Use the single quote to escape characters in text strings.
Literal

You can include literals in your format specification by enclosing the character(s) in single quotes.

Note that you must escape single quotes to include them as literals, e.g. use ''T'' to include the literal string 'T'.

Formatting Examples

The following table contains a number of examples of date time formats:

Date and Time Pattern Result
"yyyy.MM.dd G 'at' HH:mm:ss z" 2001.07.04 AD at 12:08:56 PDT
"EEE, MMM d, ''yy" Wed, Jul 4, '01
"h:mm a" 12:08 PM
"hh 'o''clock' a, zzzz" 12 o'clock PM, Pacific Daylight Time
"K:mm a, z" 0:08 PM, PDT
"yyyyy.MMMMM.dd GGG hh:mm aaa" 02001.July.04 AD 12:08 PM
"EEE, d MMM yyyy HH:mm:ss Z" Wed, 4 Jul 2001 12:08:56 -0700
"yyMMddHHmmssZ" 010704120856-0700
"yyyy-MM-dd'T'HH:mm:ss.SSSZ" 2001-07-04T12:08:56.235-0700
"yyyy-MM-dd'T'HH:mm:ss.SSSXXX" 2001-07-04T12:08:56.235-07:00
"YYYY-'W'ww-u" 2001-W27-3

Examples of Using TO_DATE

Here are several simple examples:

splice> VALUES TO_DATE('2015-01-01', 'YYYY-MM-dd');
1
----------
2015-01-01
1 row selected

splice> VALUES TO_DATE('01-01-2015', 'MM-dd-YYYY');
1
----------
2015-01-01
1 row selected

splice> VALUES (TO_DATE('01-01-2015', 'MM-dd-YYYY') + 30);
1
----------
2015-01-31
1

splice> VALUES (TO_DATE('2015-126', 'MM-DDD'));
1
----------
2015-05-06
1 row selected

splice> VALUES (TO_DATE('2015-026', 'MM-DDD'));
1
----------
2015-01-26

splice> VALUES (TO_DATE('2015-26', 'MM-DD'));
1
----------
2015-01-26
1 row selected

And here is an example that shows two interesting aspects of using TO_DATE. In this example, the input includes the literal T), which means that the format pattern must delimit that letter with single quotes. Since we’re delimiting the entire pattern in single quotes, we then have to escape those marks and specify ''T'' in our parsing pattern.

And because this example specifies a time zone (Z) in the parsing pattern but not in the input string, the timezone information is not preserved. In this case, that means that the parsed date is actually a day earlier than intended:

splice> VALUES TO_DATE('2013-06-18T01:03:30.000-0800','yyyy-MM-dd''T''HH:mm:ss.SSSZ');
1
----------
2013-06-17

The solution is to explicitly include the timezone for your locale in the input string:

splice> VALUES TO_DATE('2013-06-18T01:03:30.000-08:00','yyyy-MM-dd''T''HH:mm:ss.SSSZ');
1
----------
2013-06-18

See Also