DATE_PARSE

Parses a date and returns an object representation. Fields include the iso8601 and unix timestamp representations and numerical breakdowns. Useful for parsing dates that would be otherwise ambiguous, like 01/02/2023, before passing them to DATE to be formatted. The date is parsed based on the format string, which uses the ruby strptime (Time) syntax. If format is omitted, DATE_PARSE attempts to guess the format. Optionally, the timezone can be specified with values from the tz database.

Natural language parsing is handled by chronic.

Syntax 

DATE_PARSE(date, format, timezone)

Usage examples 

Example 1

Parse a date in EU format

Formula

DATE_PARSE("01/02/2023", "%d/%m/%Y")

Output

1
{
2
"iso8601": "2023-02-01T00:00:00+00:00",
3
"iso8601_milliseconds": "2023-02-01T00:00:00.000+00:00",
4
"unix_timestamp": 1675209600,
5
"unix_timestamp_milliseconds": 1675209600000,
6
"year": 2023,
7
"month": 2,
8
"month_name": "February",
9
"day": 1,
10
"day_of_week": 3,
11
"day_name": "Wednesday",
12
"hour": 0,
13
"minute": 0,
14
"second": 0,
15
"millisecond": 0,
16
"timezone": "UTC",
17
"utc_offset": "+00:00",
18
"utc_offset_seconds": 0
19
}

Example 2

Parse a date in US format

Formula

DATE_PARSE("01/02/2023", "%m/%d/%Y")

Output

1
{
2
"iso8601": "2023-01-02T00:00:00+00:00",
3
"iso8601_milliseconds": "2023-01-02T00:00:00.000+00:00",
4
"unix_timestamp": 1672617600,
5
"unix_timestamp_milliseconds": 1672617600000,
6
"year": 2023,
7
"month": 1,
8
"month_name": "January",
9
"day": 2,
10
"day_of_week": 1,
11
"day_name": "Monday",
12
"hour": 0,
13
"minute": 0,
14
"second": 0,
15
"millisecond": 0,
16
"timezone": "UTC",
17
"utc_offset": "+00:00",
18
"utc_offset_seconds": 0
19
}

Example 3

Parse a date in a specific timezone

Formula

DATE_PARSE("2022-01-01 12:00:00", "%Y-%m-%d %H:%M:%S", "America/Los_Angeles")

Output

1
{
2
"iso8601": "2022-01-01T12:00:00-08:00",
3
"iso8601_milliseconds": "2022-01-01T12:00:00.000-08:00",
4
"unix_timestamp": 1641067200,
5
"unix_timestamp_milliseconds": 1641067200000,
6
"year": 2022,
7
"month": 1,
8
"month_name": "January",
9
"day": 1,
10
"day_of_week": 6,
11
"day_name": "Saturday",
12
"hour": 12,
13
"minute": 0,
14
"second": 0,
15
"millisecond": 0,
16
"timezone": "PST",
17
"utc_offset": "-08:00",
18
"utc_offset_seconds": -28800
19
}

Example 4

Can be passed to DATE

Formula

DATE(DATE_PARSE("05/04/1994", "%d/%m/%Y"), "%B %e, %Y")

Output

"April 5, 1994"

Example 5

Grab specific fields via dot-notation

Formula

DATE_PARSE("01/02/202", "%m/%d/%Y").day_name

Output

"Monday"

Example 6

If no format is provided, DATE_PARSE will attempt to guess the format, just like DATE

Formula

DATE_PARSE("March 17, 2021").year

Output

2021

Example 7

Accepts unix timestamps, just like DATE

Formula

DATE_PARSE(1647712411).iso8601

Output

"2022-03-19T17:53:31+00:00"

Example 8

Accepts unix timestamps in milliseconds, just like DATE

Formula

DATE_PARSE(1687450077063).iso8601_milliseconds

Output

"2023-06-22T16:07:57.063+00:00"

Example 9

Use "now" or "today" to get the current time, just like DATE

Formula

DATE_PARSE("now").unix_timestamp_milliseconds

Output

1692791703233
Was this helpful?