Skip to main content

date_parse(format, value)

date_parse(format, value) will try to parse value using format.

If succeeding, it returns the date (and time) as a string that is in the format of %Y-m-%dT%H:%M:%s.%fZ e.g. 2020-01-01T12:32:32.000Z.

If failing, it returns null.

Example 1#

SELECT date_parse('%Y-%m-%d %H:%M:%S', '2020-01-01 12:32:48') as time

returns:

time
2020-01-01T12:32:48.000Z

Example 2#

SELECT date_parse('%Y-%m-%d %I:%M %P', '2020-01-01 07:32 pm') as time

returns

time
2020-01-01T19:32:00.000Z

Example 3#

SELECT date_parse('%Y-%m-%d', '2020-01-01') as time

returns

time
2020-01-01

Example 4#

SELECT date_parse('%m/%d/%Y', '7/3/2019') as time

returns

time
2019-07-03

Specifiers#

A specifier can be used to parse a specific part of date/time. Here is the list:

SpecifierExampleDescription
%Y2015The full proleptic Gregorian year, zero-padded to 4 digits.
%C20The proleptic Gregorian year divided by 100, zero-padded to 2 digits.
%y15The proleptic Gregorian year modulo 100, zero-padded to 2 digits.
%m07Month number (01--12), zero-padded to 2 digits.
%bJunAbbreviated month name. Always 3 letters.
%BFebruaryFull month name.
%d09Day number (01--31), zero-padded to 2 digits.
%e 9Same as %d but space-padded.
%aSunAbbreviated weekday name. Always 3 letters.
%ASundayFull weekday name.
%w1Sunday = 0, Monday = 1, ..., Saturday = 6.
%u7Monday = 1, Tuesday = 2, ..., Sunday = 7.
%U28Week number starting with Sunday (00--53), zero-padded to 2 digits.
%W27Same as %U, but week 1 starts with the first Monday in that year instead.
%G2015Same as %Y but uses the year number in ISO 8601 week date.
%g01Same as %y but uses the year number in ISO 8601 week date.
%V27Same as %U but uses the week number in ISO 8601 week date (01--53).
%j188Day of the year (001--366), zero-padded to 3 digits.
%D03/07/15Month-day-year format. Same as %m/%d/%y.
%F2015-07-03Year-month-day format (ISO 8601). Same as %Y-%m-%d.
%v3-Jul-2015Day-month-year format. Same as %e-%b-%Y.
%H21Hour number (00--23), zero-padded to 2 digits.
%k 5Same as %H but space-padded.
%I07Hour number in 12-hour clocks (01--12), zero-padded to 2 digits.
%l 7Same as %I but space-padded.
%Pamam or pm in 12-hour clocks.
%pAMAM or PM in 12-hour clocks.
%M34Minute number (00--59), zero-padded to 2 digits.
%S43Second number (00--60), zero-padded to 2 digits.
%f0325The fractional seconds (in nanoseconds) since last whole second.
%.f.0325Similar to .%f but left-aligned. These all consume the leading dot.
%R01:35Hour-minute format. Same as %H:%M.
%T01:35:17Hour-minute-second format. Same as %H:%M:%S.
%r09:35:17 AMHour-minute-second format in 12-hour clocks. Same as %I:%M:%S %p.
%ZPSTLocal time zone name. Skips all non-whitespace characters during parsing.
%z-0700Offset from the local time to UTC (with UTC being +0000).
%:z-07:00Same as %z but with a colon.
%#z-07Same as %z but allows minutes to be missing or present.
%+2015-07-02T07:35:17.036490-07:00ISO 8601 / RFC 3339 date & time format.
%s1625801245UNIX timestamp, the number of seconds since 1970-01-01 00:00 UTC.

Please note that Superintendent follows the specifiers offered by the Rust's crate: chrono.