Apache Hive : CAST…FORMAT with SQL:2016 datetime formats

Usage

CAST(<timestamp/date> AS <varchar/char/string> [FORMAT <template>])  
CAST(<varchar/char/string> AS <timestamp/date> [FORMAT <template>])

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” for numeric tokens means any non-numeric character or end of input.
• The words token and pattern are used interchangeably.

A.1. Numeric 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

YYY
Last 3 digits of a year

• Gets the prefix digit from current date.
• Can accept fewer digits than 3, similarly to YYYY.

YY
Last 2 digits of a year

• Gets the 2 prefix digits from current date.
• Can accept fewer digits than 2, similarly to YYYY.

Y
Last digit of a year

• Gets the 3 prefix digits from current date.

RRRR
4-digit rounded year

• String to datetime conversion:
• If 2 digits are provided then acts like RR.
• If 1,3 or 4 digits provided then acts like YYYY.
• For datetime to string conversion, acts like YYYY.

RR
2-digit rounded year
-String to datetime conversion:

• Semantics:
  Input: Last 2 digits of current year: First 2 digits of output:
  0 to 49 00 to 49 First 2 digits of current year
  0 to 49 50 to 99 First 2 digits of current year + 1
  50 to 99 00 to 49 First 2 digits of current year - 1
  50 to 99 50 to 99 First 2 digits of current year
• If 1-digit year is provided followed by a delimiter, falls back to YYYY with 1-digit year
  input.
• For datetime to string conversion, acts like YY.

MM
Month (1-12)

• For string to datetime conversion, conflicts with DDD, MONTH, MON.

DD
Day of month (1-31)

• For string to datetime conversion, conflicts with DDD.

DDD
Day of year (1-366)

• For string to datetime conversion, conflicts with DD and MM.

HH
Hour of day (1-12)

• If no AM/PM provided then defaults to AM.
• In string to datetime conversion, conflicts with SSSSS and HH24.

HH12
Hour of day (1-12)
See HH.

HH24
Hour of day (0-23)

• In string to datetime conversion, conflicts with SSSSS, HH12 and AM/PM.

MI
Minute of hour (0-59)

• In string to datetime conversion, conflicts with SSSSS.

SS
Second of minute (0-59)

• In string to datetime conversion, conflicts with SSSSS.

SSSSS
Second of Day (0-86399)

• In string to datetime conversion, conflicts with SS, HH, HH12, HH24, MI, AM/PM.

FF[1..9]
Fraction of second

• 1..9 indicates the number of decimal digits. “FF” (no number of digits specified) is also
  accepted.
• In datetime to string conversion, “FF” will omit trailing zeros, or output “0” if subsecond
  value is 0.
• In string to datetime conversion, fewer digits than expected are accepted if followed by a
  delimiter. “FF” acts like “FF9”.

AM|A.M.|PM|P.M.
Meridiem indicator (or AM/PM)

• Datetime to string conversion:
• AM and PM mean the exact same thing in the pattern.
  e.g. input=2019-01-01 20:00, format=“AM”, output=“PM”.
• Retains the exact format (capitalization and length) provided in the pattern string. If p.m.
  is in the pattern, we expect a.m. or p.m. in the output; if AM is in the pattern, we expect
  AM or PM in the output. If the case is mixed (Am or aM) then the output case will match the
  case of the pattern’s first character (Am => AM, aM => am).
• String to datetime conversion:
• Conflicts with HH24 and SSSSS.
• It doesn’t matter which meridian indicator is in the pattern.
  E.g. input=“2019-01-01 11:00 p.m.”, pattern=“YYYY-MM-DD HH12:MI AM”,
  output=2019-01-01 23:00:00
• If FX is enabled, input length has to match the pattern’s length. e.g. pattern=AM input=A.M.
  is not accepted, but input=pm is.
• Not listed as a character temporal because of special status: does not get padded with spaces
  upon formatting, and case is handled differently at datetime to string conversion.

D
Day of week (1-7)

• 1 means Sunday, 2 means Monday, and so on.
• Not allowed in string to datetime conversion.

Q
Quarter of year (1-4)

• Not allowed in string to datetime conversion.

WW
Aligned week of year (1-53)

• 1st week begins on January 1st and ends on January 7th, and so on.
• Not allowed in string to datetime conversion.

W
Aligned week of month (1-5)

• 1st week starts on the 1st of the month and ends on the 7th, and so on.
• Not allowed in string to datetime conversion.

IYYY
4-digit ISO 8601 week-numbering year

• Returns the year relating to the ISO week number (IW), which is the full week (Monday to
  Sunday) which contains January 4 of the Gregorian year.
• Behaves similarly to YYYY in that for datetime to string conversion, prefix digits for 1, 2,
  and 3-digit inputs are obtained from current week-numbering year.
• For string to datetime conversion, requires IW and ID|DAY|DY. Conflicts with all other date
  patterns (see “List of Date-Based Patterns”).

IYY
Last 3 digits of ISO 8601 week-numbering year

• See IYYY.
• Behaves similarly to YYY in that for datetime to string conversion, prefix digit is obtained
  from current week-numbering year and can accept 1 or 2-digit input.

IY
Last 2 digits of ISO 8601 week-numbering year

• See IYYY.
• Behaves similarly to YY in that for datetime to string conversion, prefix digits are obtained
  from current week-numbering year and can accept 1-digit input.

I
Last digit of ISO 8601 week-numbering year

• See IYYY.
• Behaves similarly to Y in that for datetime to string conversion, prefix digits are obtained
  from current week-numbering year.

IW
ISO 8601 week of year (1-53)

• Begins on the Monday closest to January 1 of the year.
• For string to datetime conversion, if the input week does not exist in the input year, an
  error will be thrown. e.g. the 2019 week-year has 52 weeks; with pattern=“iyyy-iw-id”
  input=“2019-53-2” is not accepted.
• For string to datetime conversion, requires IYYY|IYY|IY|I and ID|DAY|DY. Conflicts with all other
  date patterns (see “List of Date-Based Patterns”).

ID
ISO 8601 day of week (1-7)

• 1 is Monday, and so on.
• For string to datetime conversion, requires IYYY|IYY|IY|I and IW. Conflicts with all other
  date patterns (see “List of Date-Based Patterns”).

A.2. Character temporals

Temporal elements, but spelled out.

• For datetime to string conversion, the pattern’s case must match one of the listed formats
  (e.g. mOnTh is not accepted) to avoid ambiguity. Output is right padded with trailing spaces
  unless the pattern is marked with the fill mode modifier (FM).
• For string to datetime conversion, the case of the pattern does not matter.

MONTH|Month|month
Name of month of year

• For datetime to string conversion, will include trailing spaces up to length 9 (length of
  longest month of year name: “September”). Case is taken into account according to the
  following example (pattern => output):
• MONTH => JANUARY
• Month => January
• month => january
• For string to datetime conversion, neither the case of the pattern nor the case of the input
  are taken into account.
• For string to datetime conversion, conflicts with MM and MON.

MON|Mon|mon
Abbreviated name of month of year

• For datetime to string conversion, case is taken into account according to the following
  example (pattern => output):
• MON => JAN
• Mon => Jan
• mon => jan
• For string to datetime conversion, neither the case of the pattern nor the case of the input
  are taken into account.
• For string to datetime conversion, conflicts with MM and MONTH.

DAY|Day|day
Name of day of week

• For datetime to string conversion, will include trailing spaces until length is 9 (length of
  longest day of week name: “Wednesday”). Case is taken into account according to the following
  example (pattern => output):
• DAY = SUNDAY
• Day = Sunday
• day = sunday
• For string to datetime conversion, neither the case of the pattern nor the case of the input
  are taken into account.
• Not allowed in string to datetime conversion except with IYYY|IYY|IY|I and IW.

DY|Dy|dy
Abbreviated name of day of week

• For datetime to string conversion, case is taken into account according to the following
  example (pattern => output):
• DY = SUN
• Dy = Sun
• dy = sun
• For string to datetime conversion, neither the case of the pattern nor the case of the input
  are taken into account.
• Not allowed in string to datetime conversion except with IYYY|IYY|IY|I and IW.

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

-|.|/|,|'|;|:|
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).
• If the whole pattern string is delimited by single quotes (''), then the apostrophe separator
  (') must be escaped with a single backslash: (').

D. ISO 8601 delimiters

T|Z
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.
• For string to datetime conversion, case of input and pattern may differ.

E. Nested strings (Text)

– Surround with double quotes (") in the pattern. Note, if the whole pattern string is delimited
by double quotes, then the double quotes must be escaped with a single backslash: (").

• In order to include a literal double quote character within the nested string, the double
  quote character must be escaped with a double backslash: (\”). If the whole pattern string is
  delimited by double quotes, then escape with a triple backslash: (\")
• If the whole pattern string is delimited by single quotes, literal single
  quotes/apostrophes (') in the nested string must be escaped with a single backslash: (')
• For datetime to string conversion, we simply include the string in the output, preserving the
  characters' case.
• For string to datetime conversion, the information is lost as the nested string won’t be part
  of the resulting datetime object. However, the nested string has to match the related part of
  the input string, except case may differ.

F. Format modifier tokens

FM
Fill mode modifier

• Default 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, padding (trailing spaces for text data and leading zeroes
  for numeric data) is omitted for the temporal element immediately following an “FM” in the
  pattern string. If the element following is not a temporal element (for example, if “FM”
  precedes a separator), an error will be thrown.
  e.g. pattern=FMHH12:MI:FMSS, input=2019-01-01 01:01:01, output=1:01:1
• Modifies FX so that lack of leading zeroes are accepted for the element immediately following
  an “FM” in the pattern string.

FX
Format exact modifier

• Default for datetime to string conversion. Numeric output is left padded with zeros, and
  non-numeric output except for AM/PM is right padded with spaces up to expected length.
• Applies to the whole pattern.
• Rules applied at string to datetime conversion:
• Separators must match exactly, down to the character.
• Numeric input can’t omit leading zeroes. This rule does not apply to elements (tokens)
  immediately preceded by an “FM.”
• AM/PM input length has to match the pattern’s length. e.g. pattern=AM input=A.M. is not
  accepted, but input=pm is.