Shape / columns · All operations

Parse a date or time

Parse, format or decompose dates and times; date arithmetic; time zones.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Parse, truncate and convert zone

SQL
CREATE TEMP TABLE result AS
SELECT strptime('03/02/2026 14:30', '%d/%m/%Y %H:%M') AS parsed,
       date_trunc('month', DATE '2026-02-10') AS month,
       dayname(DATE '2026-02-10') AS weekday,
       strftime(timezone('Australia/Sydney', TIMESTAMPTZ '2026-02-10 00:00:00+00'), '%Y-%m-%d %H:%M') AS sydney;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT parsed = TIMESTAMP '2026-02-03 14:30:00' AND month = DATE '2026-02-01' AND weekday = 'Tuesday' AND sydney = '2026-02-10 11:00' FROM result)) THEN 'as expected' ELSE error('a date did not parse, truncate or convert to Sydney time as expected') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “a date did not parse, truncate or convert to Sydney time as expected”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Test a time of day

Test whether the time of day of a value lies between two bounds.

Build a date from its parts

Make a date, time or timestamp from numbers such as year, month and day, or replace one part of it.

Convert between date and time types

Move date and time values between a tool's date types, such as legacy and current types.

Set a display format

Change how date and time values are shown without changing the stored values.

Date and time function libraries

A tool's whole set of date and time functions, covering parsing, formatting, parts and arithmetic together.

Get the current date and time

Return the date or timestamp at which the run happens.

Parse text into a date or time

Turn text into a date, time or timestamp, by a format pattern or by inference.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • DateTimeread 2026-09-27

    Convert date-time data between string formats and the native date-time type

Apache DataFusion (1)
  • Time and Date Functionsread 2026-09-27

    24 functions, 5 of them aliases: date_trunc, date_part, date_bin, to_timestamp, now, …

Apache NiFi (1)
  • Date Manipulationread 2026-09-27

    11 functions: format, formatInstant, toDate, toInstant, toMicros, …

Apache SeaTunnel (1)
  • Time and Date Functionsread 2026-09-27

    24 functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP / NOW, DATEADD / TIMESTAMPADD, DATEDIFF, …

AWS Glue (3)
  • Format timestampread 2026-09-27

    Convert a timestamp column to a formatted string

  • MonthNameread 2026-09-27

    Add a column with the month name taken from a date string

  • To timestampread 2026-09-27

    Convert a column to timestamp type

Azure Data Factory (2)
  • Date and time functionsread 2026-09-27

    34 functions: add, addDays, addMonths, between, currentDate, …

  • Date functionsread 2026-09-27

    20 functions: addDays, addHours, addMinutes, addSeconds, addToTime, …

dbt-utils (1)
DuckDB (7)
Duckle (10)
  • Current Timestampread 2026-09-25

    add the run time as a column

  • Date Addread 2026-09-25

    add an interval to a date

  • Date Diffread 2026-09-25

    difference between two dates

  • Epoch Convertread 2026-09-25

    convert between timestamp and Unix epoch seconds

  • Extract Partread 2026-09-25

    extract a date part

  • Format Dateread 2026-09-25

    format a date or timestamp as text

  • Parse Dateread 2026-09-25

    parse text to a date or timestamp

  • Time Binread 2026-09-25

    round timestamps down to fixed-interval buckets

  • Timezone Convertread 2026-09-25

    convert a timestamp between time zones with AT TIME ZONE

  • Truncateread 2026-09-25

    truncate a timestamp to a unit

EasyMorph (1)
Ibis (49)
  • BetweenTimeread 2026-09-27

    Test whether the time of day of a value lies between two bounds

  • Dateread 2026-09-27

    Date part of a timestamp

  • DateAddread 2026-09-27

    Add an interval to a date

  • DateDeltaread 2026-09-27

    Difference between two dates as a whole number of a chosen unit

  • DateDiffread 2026-09-27

    Difference between two dates, as an interval

  • DateFromYMDread 2026-09-27

    Build a date from year, month and day

  • DateNowread 2026-09-27

    Current date

  • DateSubread 2026-09-27

    Subtract an interval from a date

  • DateTruncateread 2026-09-27

    Truncate a date to a unit such as month or year

  • DayOfWeekIndexread 2026-09-27

    Day of the week of a date or timestamp, as a number

  • DayOfWeekNameread 2026-09-27

    Day of the week of a date or timestamp, as a name

  • ExtractDayread 2026-09-27

    Day of the month of a date or timestamp

  • ExtractDayOfYearread 2026-09-27

    Day of the year of a date or timestamp

  • ExtractEpochSecondsread 2026-09-27

    Seconds since the UNIX epoch of a date or timestamp

  • ExtractHourread 2026-09-27

    Hour of a time or timestamp

  • ExtractIsoYearread 2026-09-27

    ISO year of a date or timestamp

  • ExtractMicrosecondread 2026-09-27

    Microsecond of a time or timestamp

  • ExtractMillisecondread 2026-09-27

    Millisecond of a time or timestamp

  • ExtractMinuteread 2026-09-27

    Minute of a time or timestamp

  • ExtractMonthread 2026-09-27

    Month of a date or timestamp

  • ExtractQuarterread 2026-09-27

    Quarter of a date or timestamp

  • ExtractSecondread 2026-09-27

    Second of a time or timestamp

  • ExtractWeekOfYearread 2026-09-27

    Week of the year of a date or timestamp

  • ExtractYearread 2026-09-27

    Year of a date or timestamp

  • IntervalAddread 2026-09-27

    Add two intervals

  • IntervalFloorDivideread 2026-09-27

    Divide an interval by a number, rounding down

  • IntervalFromIntegerread 2026-09-27

    Build an interval from an integer and a unit

  • IntervalMultiplyread 2026-09-27

    Multiply an interval by a number

  • IntervalSubtractread 2026-09-27

    Subtract one interval from another

  • Strftimeread 2026-09-27

    Format a date, time or timestamp as a string by a format string

  • StringToDateread 2026-09-27

    Parse a string into a date by a format string

  • StringToTimeread 2026-09-27

    Parse a string into a time by a format string

  • StringToTimestampread 2026-09-27

    Parse a string into a timestamp by a format string

  • Timeread 2026-09-27

    Time part of a timestamp

  • TimeAddread 2026-09-27

    Add an interval to a time

  • TimeDeltaread 2026-09-27

    Difference between two times as a whole number of a chosen unit

  • TimeDiffread 2026-09-27

    Difference between two times, as an interval

  • TimeFromHMSread 2026-09-27

    Build a time from hours, minutes and seconds

  • TimeSubread 2026-09-27

    Subtract an interval from a time

  • TimeTruncateread 2026-09-27

    Truncate a time to a unit such as hour or minute

  • TimestampAddread 2026-09-27

    Add an interval to a timestamp

  • TimestampBucketread 2026-09-27

    Floor a timestamp to buckets of a fixed interval, with an optional offset

  • TimestampDeltaread 2026-09-27

    Difference between two timestamps as a whole number of a chosen unit

  • TimestampDiffread 2026-09-27

    Difference between two timestamps, as an interval

  • TimestampFromUNIXread 2026-09-27

    Build a timestamp from a UNIX epoch number in a given unit

  • TimestampFromYMDHMSread 2026-09-27

    Build a timestamp from year, month, day, hour, minute and second

  • TimestampNowread 2026-09-27

    Current timestamp

  • TimestampSubread 2026-09-27

    Subtract an interval from a timestamp

  • TimestampTruncateread 2026-09-27

    Truncate a timestamp to a unit such as day or hour

KNIME Analytics Platform (20)
OpenRefine (1)
  • Date functionsread 2026-09-27

    6 functions: now, toDate, diff, inc, datePart

Polars (6)
Power Query M (5)
  • Date functionsread 2026-09-27

    58 functions: Date.FromText, Date.AddMonths, Date.StartOfWeek, Date.IsInPreviousNDays, Date.WeekOfYear

  • DateTime functionsread 2026-09-27

    26 functions: DateTime.FromText, DateTime.LocalNow, DateTime.Date, DateTime.AddZone, DateTime.IsInNextNHours

  • DateTimeZone functionsread 2026-09-27

    16 functions: DateTimeZone.SwitchZone, DateTimeZone.ToUtc, DateTimeZone.ToLocal, DateTimeZone.RemoveZone, DateTimeZone.UtcNow

  • Duration functionsread 2026-09-27

    13 functions: Duration.From, Duration.FromText, Duration.Days, Duration.TotalHours, Duration.ToText

  • Time functionsread 2026-09-27

    10 functions: Time.From, Time.FromText, Time.Hour, Time.StartOfHour, Time.ToText

RapidMiner Studio (5)
  • Adjust Dateread 2026-09-27

    Shift dates by adding or subtracting an amount of time

  • Date to Nominalread 2026-09-27

    Format a date column as nominal text using a date format pattern

  • Date to Numericalread 2026-09-27

    Convert a date column to a number: epoch offset or an extracted component such as month

  • Nominal to Dateread 2026-09-27

    Parse text values into date, time or date-time using a format pattern

  • Numerical to Dateread 2026-09-27

    Convert milliseconds since epoch into date-time values

Tableau Prep (3)
  • Convert Datesread 2026-09-27

    Convert a date field to its year, quarter, month, week, day, weekday name or month name

  • Custom Fiscal Yearread 2026-09-27

    Convert dates using a fiscal year that starts in a chosen month

  • Date functions (group)read 2026-09-27

    12 functions: DATEADD, DATEDIFF, DATEPARSE, DATETRUNC, NOW; offset, difference, parse, truncate, parts

Trifacta (2)
  • Date Functions (group)read 2026-09-27

    40 functions: DATEDIF, DATEFORMAT, PARSEDATE, NETWORKDAYS, CONVERTTOUTC; build, parse, format, decompose and offset dates

  • Date formatread 2026-09-27

    Change the display format of Datetime columns

VisiData (2)
  • type-customdateread 2026-09-23

    Set type of current column to date parsed with a given strptime format

  • type-customdate-selectedread 2026-09-23

    Set type of selected source columns to date parsed with a given format

Background

In DuckDB, strftime and strptime convert between DATE or TIMESTAMP values and strings, often needed when parsing CSV files or moving data between programs.[1] In DuckDB, strptime converts a string to a timestamp by a format string, so strptime('02/03/1992', '%d/%m/%Y') gives 1992-03-02 00:00:00.[1]

When to use it

DuckDB's strptime throws an error when a string does not match its format, while try_strptime returns NULL instead.[1] Parse with the erroring form while you are still learning a column's formats, and switch to the NULL-returning form only once you count the NULLs it leaves behind. (Meridian's view) DuckDB's strptime also accepts a list of format strings and applies them in turn until one succeeds.[2] Settle whether a column is written day first or month first before parsing it, rather than handing both patterns to one list of formats. (Meridian's view) In DuckDB, the two-digit year specifier %y turns 00 to 68 into 2000 to 2068, and 69 to 99 into 1969 to 1999.[1]

ISO 8601, an international standard maintained by the International Organization for Standardization (ISO) and first published in 1988, provides an unambiguous way of representing dates, especially to avoid misinterpreting numeric dates transferred between countries with different conventions.[3] ISO 8601 orders date and time values from the largest unit to the smallest, so their lexicographical order matches chronological order, except for negative years or time offsets.[3] In DuckDB, a DATE literal must be written in the ISO 8601 format YYYY-MM-DD.[4] Write dates for other programs in ISO 8601's year-month-day order. (Meridian's view)

In DuckDB, a TIMESTAMP is more commonly used for local observations of time recorded in an unspecified time zone, and turning such values into unambiguous instants is a common data cleaning problem.[5] Find out which zone a column's clock times were recorded in before comparing them with times from another source. (Meridian's view)

The method

DuckDB's read_csv function and COPY statement accept a DATEFORMAT and a TIMESTAMPFORMAT, so dates can be parsed by a pattern as a file is read.[1] DuckDB counts dates in the Gregorian calendar only, following the SQL standard's lead, even for years before that calendar was in use.[4] DuckDB also accepts the strings infinity and -infinity as dates later and earlier than all others, and epoch as shorthand for 1970-01-01.[4]

In DuckDB, both TIMESTAMP and TIMESTAMP WITH TIME ZONE store a 64-bit integer count of non-leap microseconds since the Unix epoch, 1970-01-01 00:00:00+00.[5] In DuckDB, TIMESTAMP arithmetic, binning and formatting follow the plain rules of Coordinated Universal Time (UTC) without offsets, while TIMESTAMP WITH TIME ZONE does them in a configured time zone, which defaults to the system time zone.[5]

Sources

  1. [1]Date Format Functions – DuckDBdocumentation
  2. [2]Timestamp Functions – DuckDBdocumentation
  3. [3]ISO 8601 - Wikipediasecondary source
  4. [4]Date Types – DuckDBdocumentation
  5. [5]Timestamp Types – DuckDBdocumentation

Types it works with

FineType's registry gives 69 types a format string.

TypeExpression
Abbreviated Month Day Year (no comma)%b %d %Y
Abbreviated Month Date%b %d, %Y
Compact Date (DDMMYYYY)%d%m%Y
Compact Date (MMDDYYYY)%m%d%Y
Compact Year-Month (YYYYMM)%Y%m
Compact Date (YYYYMMDD)%Y%m%d
DMY Date (dash-separated)%d-%m-%Y
DMY Date with Abbreviated Month (dash-separated)%d-%b-%Y
DMY Date with Abbreviated Month (dash, 2-digit year)%d-%b-%y
DMY Date (dot-separated)%d.%m.%Y
DMY Short Date (dot, 2-digit year)%d.%m.%y
DMY Short Date (slash, 2-digit year)%d/%m/%y
DMY Date (slash-separated)%d/%m/%Y
DMY Date with Abbreviated Month (space-separated)%d %b %Y
DMY Date with Full Month (space-separated)%d %B %Y
Full Month Day Year (no comma)%B %d %Y
ISO Date%Y-%m-%d
ISO Week Date%G-W%V
Long Date with Full Month Name%B %d, %Y
MDY Date (dash-separated)%m-%d-%Y
MDY Short Date (slash, 2-digit year)%m/%d/%y
MDY Date (slash-separated)%m/%d/%Y
Abbreviated Month Year%b %Y
Full Month Year%B %Y
Month/Year (slash-separated)%m/%Y
Ordinal Date%Y-%j
Short Date (Day-Month-Year, 2-digit year)%d-%m-%y
Short Date (Month-Day-Year, 2-digit year)%m-%d-%y
Short Date (Year-Month-Day, 2-digit year)%y-%m-%d
Full Weekday with Abbreviated Month%A, %b %d, %Y
Weekday DMY Full (formal)%A, %d %B %Y
Full Weekday with Full Month Name%A, %B %d, %Y
Year-Month (ISO truncated)%Y-%m
YMD Date (dot-separated)%Y.%m.%d
YMD Date (slash-separated)%Y/%m/%d
12-Hour Time (HH:MM AM/PM)%I:%M %p
24-Hour Time (HH:MM)%H:%M
12-Hour Time with AM/PM%I:%M:%S %p
24-Hour Time (HH:MM:SS)%H:%M:%S
ISO Time with Microseconds%H:%M:%S.%f
Common Log Format (Apache/Nginx)%d/%b/%Y:%H:%M:%S %z
C ctime / asctime Format%a %b %-d %H:%M:%S %Y
DMY Hour-Minute Datetime%d/%m/%Y %H:%M
European Dot Timestamp (DMY 24h)%d.%m.%Y %H:%M:%S
Dot YMD Timestamp (24h)%Y.%m.%d %H:%M:%S
ISO 8601%Y-%m-%dT%H:%M:%SZ
ISO 8601 Compact%Y%m%dT%H%M%S
ISO 8601 with Microseconds and Offset%Y-%m-%dT%H:%M:%S.%f%z
ISO 8601 with Microseconds%Y-%m-%dT%H:%M:%S.%fZ
ISO 8601 with Milliseconds and Offset%Y-%m-%dT%H:%M:%S.%g%z
ISO 8601 with Milliseconds%Y-%m-%dT%H:%M:%S.%gZ
ISO 8601 with Timezone Offset%Y-%m-%dT%H:%M:%S%z
ISO Datetime with Microseconds (no timezone)%Y-%m-%dT%H:%M:%S.%f
ISO Datetime with Milliseconds (no timezone)%Y-%m-%dT%H:%M:%S.%g
ISO Datetime, minute precision (no timezone)%Y-%m-%dT%H:%M
ISO Datetime, second precision (no timezone)%Y-%m-%dT%H:%M:%S
ISO with Space Separator and Z%Y-%m-%d %H:%M:%SZ
MDY 12-Hour Datetime%m/%d/%Y %I:%M %p
MDY 24-Hour Datetime%m/%d/%Y %H:%M:%S
PostgreSQL Short Offset%Y-%m-%d %H:%M:%S.%f%z
RFC 2822%a, %d %b %Y %H:%M:%S %z
RFC 3339 (Space Separator)%Y-%m-%d %H:%M:%S%z
Slash YMD Timestamp (24h)%Y/%m/%d %H:%M:%S
SQL Timestamp with Microseconds%Y-%m-%d %H:%M:%S.%f
SQL Timestamp with Microseconds and Offset%Y-%m-%d %H:%M:%S.%f%z
SQL Timestamp with Milliseconds%Y-%m-%d %H:%M:%S.%g
SQL Minute-Precision Datetime%Y-%m-%d %H:%M
SQL Standard%Y-%m-%d %H:%M:%S
BSD Syslog (RFC 3164)%b %-d %H:%M:%S

Used in

7 Protocols use this operation: