Cast...Format
SQL:2016 also introduced the FORMAT clause for CAST which is the standard way to do string <-> datetime conversions
Usage
...
CAST(<datetime><timestamp/date> AS <char string type><varchar/char/string> [FORMAT <template>])
CAST(<<varchar/char /string> AS <datetime<timestamp/date> type> [FORMAT <template>])
Example
For example: select cast(dt as string format 'DD-MM-YYYY')
select cast('01-05-2017' as date format 'DD-MM-YYYY')
Template elements, a.k.a. Tokens, a.k.a Patterns a.k.a SQL:2016 Datetime Formats
Notes
For all tokens:
- Patterns are case-insensitive, except AM/PM and T/Z. See these sections for more details.
- For string to datetime conversion, no duplicate format tokens are allowed, including tokens
that have the same meaning but different lengths ("Y" and "YY" conflict) or different
behaviors ("RR" and "YY" conflict).
For all numeric tokens:
- The "expected length" of input/output is the number of tokens in the character (e.g. "YYY": 3,
"Y": 1, and so on), with some exceptions (see map SPECIAL_LENGTHS).
- For string to datetime conversion, inputs of fewer digits than expected are accepted if
followed by a delimiter, e.g. format="YYYY-MM-DD", input="19-1-1", output=2019-01-01 00:00:00.
- For datetime to string conversion, output is left padded with zeros, e.g. format="DD SSSSS",
input=2019-01-01 00:00:03, output="01 00003".
Accepted format tokens
...
Note: "|" means "or". "Delimiter" means a separator, tokens T or Z, or end of input.
A. Temporal tokens
YYYY
4-digit year
- For string to datetime conversion, prefix digits for 1, 2, and 3-digit inputs are obtained
from current date
E.g. input=‘9-01-01’, pattern =‘YYYY-MM-DD’, current year=2020, output=2029-01-01 00:00:00
...
PM|P.M.
Meridiem indicator
See AM|A.M.
B. Time zone tokens
TZH
Time zone offset hour (-15 to +15)
- 3-character-long input is expected: 1 character for the sign and 2 digits for the value.
e.g. “+10”, “-05”
- 2-digit input is accepted without the sign, e.g. “04”.
- Both these 2 and 3-digit versions are accepted even if not followed by separators.
- Disabled for timestamp to string and date to string conversion, as timestamp and date are time
zone agnostic.
TZM
Time zone offset minute (0-59)
- For string to datetime conversion:
- TZH token is required.
- Unsigned; sign comes from TZH.
- Therefore time zone offsets like “-30” minutes should be expressed thus: input=“-00:30”
pattern=“TZH:TZM”.
- Disabled for timestamp to string and date to string conversion, as timestamp and date are time
zone agnostic.
C. Separators
-|.|/|,|'|;|:|<space>
Separator
- Uses loose matching. Existence of a sequence of separators in the format should match the
existence of a sequence of separators in the input regardless of the types of the separator or
the length of the sequence where length > 1. E.g. input=“2019-. ;10/10”, pattern=“YYYY-MM-DD”
is valid; input=“20191010”, pattern=“YYYY-MM-DD” is not valid.
- If the last separator character in the separator substring is "-" and is immediately followed
by a time zone hour (tzh) token, it's a negative sign and not counted as a separator, UNLESS
this is the only possible separator character in the separator substring (in which case it is
not counted as the tzh's negative sign).
D. ISO 8601 delimiters
T
ISO 8601 delimiter
- Serves as a delimiter.
- Function is to support formats like “YYYY-MM-DDTHH24:MI:SS.FF9Z”, “YYYY-MM-DD-HH24:MI:SSZ”
- For datetime to string conversion, output is always capitalized ("T"), even if lowercase ("t")
is provided in the pattern.
Z
ISO 8601 delimiter
See T.v