---
title: "strptime"
slug: "dev-database-reference-sql-functions-date-time-strptime"
updated: 2026-10-30T18:19:01Z
published: 2026-10-30T18:19:01Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-date-time-strptime"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# strptime

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time) › Parsing and formatting.* Parses text into a timestamp, using a format made of `%` codes such as `'%d/%m/%Y'`, or a list of formats to try in turn. Text that doesn't match stops the query with an error; [try_strptime](/develop/docs/dev-database-reference-sql-functions-date-time-try-strptime) returns NULL instead.

## Syntax

```
strptime(text, format)
strptime(text, [format, ...])
```

| Argument | Type | Description |
| --- | --- | --- |
| `text` | `VARCHAR` | The text to parse. |
| `format` | `VARCHAR` constant, or a list of them | The format, made of `%` codes and literal characters. With a list, each format is tried in order and the first that matches is used. |

**Returns:** `TIMESTAMP`. With `%z` in the format (in any format of a list), `TIMESTAMP WITH TIME ZONE`, converted to UTC. With `%n`, `TIMESTAMP_NS`.

## Set up the examples

The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_date_time_strptime`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the `run()` and `plan()` helpers, and creates a table of seven imported rows. Each has a date as text (`s`), in whatever form it arrived, and a load time (`ts`). Row 4 has no values:

```
import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_date_time_strptime"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.imports"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, s VARCHAR, ts TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '2026-02-28', TIMESTAMP '2026-02-28 23:15:00'), "
    "(2, '01/03/2026', TIMESTAMP '2026-03-01 08:30:45.123456'), "
    "(3, 'not a date', TIMESTAMP '2024-12-30 12:00:00'), "
    "(4, NULL, NULL), "
    "(5, '', TIMESTAMP '2024-02-29 00:00:00'), "
    "(6, '2026-02-30', TIMESTAMP '1969-12-31 23:59:59.999999'), "
    "(7, ' 2026-02-28 ', TIMESTAMP '2027-01-03 00:00:00.5')")
```

```
[]
[]
[(7,)]
```

## What it does

`strptime` reads the text from left to right against the format. A `%` code reads one field; any other character must appear in the text as written. The parts the format doesn't name take their first value: a format with no date gives 1 January 1900, and one with no time gives midnight.

| Code | Reads | Example text |
| --- | --- | --- |
| `%Y` | Year, up to four digits | `2024` |
| `%y` | Two-digit year: `68` is 2068 and `69` is 1969 | `24` |
| `%m`, `%d` | Month, day of the month; one or two digits | `02`, `2`, `29` |
| `%B`, `%b` | English month name, full or short | `February`, `feb` |
| `%A`, `%a` | English day name, full or short; it isn't checked against the date | `Thursday`, `thu` |
| `%j` | Day of the year | `060` |
| `%G`, `%V` | ISO year and ISO week; the date is that week's Monday | `2024W09` with `&apos;%GW%V&apos;` |
| `%H`, `%M`, `%S` | Hour (0 to 23), minute, second | `13:45:30` |
| `%I`, `%p` | Hour on the 12-hour clock (1 to 12), and `AM` or `PM` | `1:45 PM` |
| `%T` | `%H:%M:%S` | `13:45:30` |
| `%g`, `%f`, `%n` | Milliseconds, microseconds, nanoseconds: the digits after the decimal point | `123`, `123456`, `123456789` |
| `%z` | A UTC offset: `+HH`, `+HHMM` or `+HH:MM` | `+02:30` |
| `%Z` | A time-zone name, which is read and ignored | `UTC`, `Europe/Lisbon` |
| `%%` | A literal `%` |  |

`%-m`, `%-d` and the other `%-` forms are accepted too. Spaces before and after the text are skipped.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| NULL text, a NULL format or a NULL list | `NULL` |
| `strptime(&apos;2023-02-29&apos;, &apos;%Y-%m-%d&apos;)`, a date that doesn't exist | Error: `Date out of range: 2023-2-29` |
| `strptime(&apos;366-2023&apos;, &apos;%j-%Y&apos;)` | `2024-01-01`: day 366 of a 365-day year runs into the next year |
| `strptime(&apos;Mon 29 Feb 2024&apos;, &apos;%a %d %b %Y&apos;)` (29 February 2024 was a Thursday) | `2024-02-29`: the day name isn't checked |
| `strptime(&apos;2024-02-29 13:45:30.5&apos;, &apos;%Y-%m-%d %H:%M:%S.%f&apos;)` | `13:45:30.000005`: `%f` reads `5` as 5 microseconds, not half a second |
| `strptime(&apos;2024-02-29 13:45:30 Europe/Lisbon&apos;, &apos;%Y-%m-%d %H:%M:%S %Z&apos;)` | `2024-02-29 13:45:30`, a `TIMESTAMP`: the zone name isn't applied |
| `strptime(&apos;13:00 PM&apos;, &apos;%I:%M %p&apos;)` | Error: `Could not parse string &quot;13:00 PM&quot; according to format specifier &quot;%I:%M %p&quot;`, then `Hour12 out of range, expected a value between 1 and 12` |
| `strptime(&apos;2024-02-29 13:45:30Z&apos;, &apos;%Y-%m-%d %H:%M:%S%z&apos;)` | Error: `Could not parse string ...`, then `Expected +HH[MM] or -HH[MM]` |
| `strptime(&apos;&apos;, &apos;%Y-%m-%d&apos;)` (an empty string) | Error: `Could not parse string &quot;&quot; ...`, then `Expected a number` |
| `strptime(&apos;2024-02-29&apos;, &apos;%Y-%m&apos;)`, text left over | Error: `Could not parse string ...`, then `Full specifier did not match: trailing characters` |
| `24:00:00` or `23:59:60` with `&apos;%H:%M:%S&apos;` | Error: `Hour out of range` or `Seconds out of range` |
| A year past 9999, or before year 1 | Error: `Could not parse string ...` |
| A `DATE`, a number or a `BLOB` instead of text | Error: `No function matches the given name and argument types`, for example `&apos;strptime(DATE, STRING_LITERAL)&apos;` |

The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-date-time-strptime#set-up-the-examples):

```
run("SELECT strptime(NULL, '%Y-%m-%d'), strptime('2024-02-29', NULL), strptime('2024-02-29', NULL::VARCHAR[])")
run("SELECT strptime('366-2023', '%j-%Y'), strptime('Mon 29 Feb 2024', '%a %d %b %Y'), "
    "strptime('2024-02-29 13:45:30.5', '%Y-%m-%d %H:%M:%S.%f'), "
    "strptime('2024-02-29 13:45:30 Europe/Lisbon', '%Y-%m-%d %H:%M:%S %Z')")
run("SELECT strptime(' 2024-02-29 ', '%Y-%m-%d'), strptime('2024-2-9', '%Y-%m-%d'), strptime('1:45 PM', '%I:%M %p'), "
    "strptime('69-01-01', '%y-%m-%d'), strptime('68-01-01', '%y-%m-%d'), strptime('2024W09', '%GW%V')")
run("SELECT strptime('2023-02-29', '%Y-%m-%d')")
run("SELECT strptime('13:00 PM', '%I:%M %p')")
run("SELECT strptime('2024-02-29 13:45:30Z', '%Y-%m-%d %H:%M:%S%z')")
run("SELECT strptime('', '%Y-%m-%d')")
run("SELECT strptime('2024-02-29', '%Y-%m')")
run("SELECT strptime('10000-01-01', '%Y-%m-%d')")
run("SELECT strptime(DATE '2024-02-29', '%Y-%m-%d')")
```

```
[(None, None, None)]
[(datetime.datetime(2024, 1, 1, 0, 0), datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2024, 2, 29, 13, 45, 30, 5), datetime.datetime(2024, 2, 29, 13, 45, 30))]
[(datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2024, 2, 9, 0, 0), datetime.datetime(1900, 1, 1, 13, 45), datetime.datetime(1969, 1, 1, 0, 0), datetime.datetime(2068, 1, 1, 0, 0), datetime.datetime(2024, 2, 26, 0, 0))]
OSError: Date out of range: 2023-2-29
OSError: Could not parse string "13:00 PM" according to format specifier "%I:%M %p"
OSError: Could not parse string "2024-02-29 13:45:30Z" according to format specifier "%Y-%m-%d %H:%M:%S%z"
OSError: Could not parse string "" according to format specifier "%Y-%m-%d"
OSError: Could not parse string "2024-02-29" according to format specifier "%Y-%m"
OSError: Could not parse string "10000-01-01" according to format specifier "%Y-%m-%d"
InternalError: Binder Error: No function matches the given name and argument types 'strptime(DATE, STRING_LITERAL)'. You might need to add explicit type casts.
```

## Examples

Parse the first row's text, which is in `YYYY-MM-DD` form:

```
run(f"SELECT id, s, strptime(s, '%Y-%m-%d') FROM {TABLE} WHERE id = 1")
```

```
[(1, '2026-02-28', datetime.datetime(2026, 2, 28, 0, 0))]
```

The first two rows hold dates in two forms. Give both formats in a list:

```
run(f"SELECT id, s, strptime(s, ['%Y-%m-%d', '%d/%m/%Y']) FROM {TABLE} WHERE id <= 2 ORDER BY id LIMIT 10")
```

```
[(1, '2026-02-28', datetime.datetime(2026, 2, 28, 0, 0)), (2, '01/03/2026', datetime.datetime(2026, 3, 1, 0, 0))]
```

The order of the list decides text that matches more than one format. `01/02/2024` is 1 February with the day first, and 2 January with the month first:

```
run("SELECT strptime('01/02/2024', ['%d/%m/%Y', '%m/%d/%Y']), strptime('01/02/2024', ['%m/%d/%Y', '%d/%m/%Y'])")
```

```
[(datetime.datetime(2024, 2, 1, 0, 0), datetime.datetime(2024, 1, 2, 0, 0))]
```

A date and time with a UTC offset. With `%z`, the result is a `TIMESTAMP WITH TIME ZONE` in UTC, which the ADBC driver returns as a `datetime` in UTC: 13:45:30 at +02:30 is 11:15:30 UTC:

```
run("SELECT strptime('2024-02-29 13:45:30+02:30', '%Y-%m-%d %H:%M:%S%z'), "
    "strptime('2024-02-29T13:45:30.123456', '%Y-%m-%dT%H:%M:%S.%f')")
```

```
[(datetime.datetime(2024, 2, 29, 11, 15, 30, tzinfo=zoneinfo.ZoneInfo(key='UTC')), datetime.datetime(2024, 2, 29, 13, 45, 30, 123456))]
```

With `%n`, the result has nanoseconds, which Python's `datetime` can't hold, so cast it to `VARCHAR` to read it:

```
run("SELECT CAST(strptime('2024-02-29 13:45:30.123456789', '%Y-%m-%d %H:%M:%S.%n') AS VARCHAR)")
```

```
[('2024-02-29 13:45:30.123456789',)]
```

On the whole column, the query stops at the first text that doesn't match. Use [try_strptime](/develop/docs/dev-database-reference-sql-functions-date-time-try-strptime) to get NULL for those rows instead:

```
run(f"SELECT id, strptime(s, '%Y-%m-%d') FROM {TABLE} ORDER BY id LIMIT 10")
```

```
OSError: Could not parse string "01/03/2026" according to format specifier "%Y-%m-%d"
```

## How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so `strptime()` of a column is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine parses the text of each row it returns. A condition that VAST DataBase evaluates, such as `id = 1` above, runs first, so `strptime()` never sees the rows it removes.
- **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine parses the text and keeps the rows that match. No rows are skipped in storage.

The plan of a condition on `strptime(s, ...)` shows the `FILTER` step above the scan:

```
plan(f"SELECT id FROM {TABLE} WHERE strptime(s, '%Y-%m-%d') > TIMESTAMP '2026-01-01 00:00:00'")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (strptime(s, '%Y-%m-%d') >│
│   '2026-01-01 00:00:00':  │
│        :TIMESTAMP)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             s             │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Parse constants, not columns.** `strptime()` of a constant is computed once, before the scan, so `ts &gt; strptime(&apos;2026-03-01&apos;, &apos;%Y-%m-%d&apos;)` is a comparison of the raw column with a constant and is pushed down to VAST DataBase. The plan has no `FILTER` step:

```
plan(f"SELECT id FROM {TABLE} WHERE ts > strptime('2026-03-01', '%Y-%m-%d')")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

- **Store timestamps as `TIMESTAMP`, not as text.** Parse the text once, when you load it, and keep the result in a `TIMESTAMP` column. A filter on that column with a constant is pushed down; a filter on `strptime(s, ...)` parses every row in the Query Engine.

## Limitations and errors

- **The format must be a constant.** A format taken from a column fails with `Invalid Input Error: strptime format must be a constant`. An empty list fails with `strptime format must be a string`, and an empty format with `Failed to parse format specifier : Empty format string`.
- **Not every `%` code.** `%s` (epoch seconds), `%F` and others fail with `Invalid Input Error: Failed to parse format specifier %s: Unrecognized format for strftime/strptime: %s`. A list that contains such a code fails even if another format would match. For epoch seconds, use [to_timestamp](/develop/docs/dev-database-reference-sql-functions-date-time-to-timestamp); for `%F`, write `%Y-%m-%d`.
- **`%f` reads a number of microseconds.** `.5` gives 5 microseconds and `.1234567` fails. When the text can have fewer than six digits after the point, pad it first, or use `%g` for exactly three.
- **English names and ASCII digits only.** A month name in another language, or digits from another script, fails with `Could not parse string`.
- **Time-zone names aren't applied.** `%Z` reads a name and ignores it; only `%z` offsets are converted to UTC. `Z` for UTC isn't accepted by `%z`. See [time zones](/develop/docs/dev-database-reference-sql-functions-date-time-time-zones).
- **The error shows only the first format of a list.** When no format of a list matches, the error names the first one, for example `according to format specifier &quot;%Y&quot;`.

```
run(f"SELECT id, strptime(s, s) FROM {TABLE} WHERE id = 1")
run("SELECT strptime('1709214330', '%s')")
run("SELECT strptime('2024-02-29', ['%Q', '%Y-%m-%d'])")
run("SELECT strptime('F\u00e9vrier 2024', '%B %Y')")
run("SELECT strptime('x', ['%Y', '%d/%m/%Y'])")
```

```
InternalError: Invalid Input Error: strptime format must be a constant
InternalError: Invalid Input Error: Failed to parse format specifier %s: Unrecognized format for strftime/strptime: %s
InternalError: Invalid Input Error: Failed to parse format specifier %Q: Unrecognized format for strftime/strptime: %Q
OSError: Could not parse string "Février 2024" according to format specifier "%B %Y"
OSError: Could not parse string "x" according to format specifier "%Y"
```

## Similar functions

| Function | What it does | On `&apos;2023-02-29&apos;` |
| --- | --- | --- |
| [try_strptime](/develop/docs/dev-database-reference-sql-functions-date-time-try-strptime) | The same parsing; returns NULL for text that doesn't match | `NULL` |
| `CAST(s AS TIMESTAMP)`, `CAST(s AS DATE)` | Parse ISO text (`2024-02-29`, `2024-02-29 13:45:30`) without a format; `TRY_CAST` returns NULL instead of failing ([conditional and cast functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast)) | `TRY_CAST`: `NULL` |
| [strftime](/develop/docs/dev-database-reference-sql-functions-date-time-strftime) | The reverse: formats a date or timestamp as text, with the same codes |  |
| [to_timestamp](/develop/docs/dev-database-reference-sql-functions-date-time-to-timestamp) | Turns epoch seconds into a timestamp |  |
| [make_timestamp](/develop/docs/dev-database-reference-sql-functions-date-time-make-timestamp) | Builds a timestamp from numbers |  |

```
run("SELECT strptime('2024-02-29', '%Y-%m-%d'), CAST('2024-02-29' AS TIMESTAMP), CAST('2024-02-29' AS DATE)")
run("SELECT try_strptime('2023-02-29', '%Y-%m-%d'), TRY_CAST('2023-02-29' AS DATE), TRY_CAST('29/02/2024' AS DATE)")
```

```
[(datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2024, 2, 29, 0, 0), datetime.date(2024, 2, 29))]
[(None, None, None)]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time): all the date and time functions, and [parsing and formatting](/develop/docs/dev-database-reference-sql-functions-date-time#parsing-and-formatting).
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.