try_strptime

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Parsing and formatting. Parses text into a timestamp, like strptime, but returns NULL for text that doesn't match the format instead of stopping the query.

Syntax

try_strptime(text, format)
try_strptime(text, [format, ...])

Returns: TIMESTAMP, or NULL when the text doesn't match. With %z in the format, TIMESTAMP WITH TIME ZONE, converted to UTC.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_date_time_try_strptime. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), 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_try_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

try_strptime parses text exactly as strptime does, with the same % codes and the same results for text that matches. The difference is what happens when the text doesn't match the format or names a date that doesn't exist: strptime fails the whole query, and try_strptime returns NULL for that value and carries on.

It returns NULL only for the text. A problem with the format itself, such as an unknown % code, an empty format or a format taken from a column, still fails the query, because no row could be parsed with it.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT try_strptime(NULL, '%Y-%m-%d'), try_strptime('2024-02-29', NULL), try_strptime('', '%Y-%m-%d'), "
    "try_strptime('2023-02-29', '%Y-%m-%d'), try_strptime('13:00 PM', '%I:%M %p'), try_strptime('24:00:00', '%H:%M:%S')")
run("SELECT try_strptime('2024-02-29 13:45:30Z', '%Y-%m-%d %H:%M:%S%z'), try_strptime('2024-02-29', '%Y-%m'), "
    "try_strptime('x', ['%Y', '%d/%m/%Y']), try_strptime(' 2024-02-29 ', '%Y-%m-%d')")
run("SELECT try_strptime('2024', '%Q')")
run("SELECT try_strptime('2024-02-29', '')")
run("SELECT try_strptime('2024-02-29', [])")
run("SELECT try_strptime(DATE '2024-02-29', '%Y-%m-%d')")

[(None, None, None, None, None, None)]
[(None, None, None, datetime.datetime(2024, 2, 29, 0, 0))]
InternalError: Invalid Input Error: Failed to parse format specifier %Q: Unrecognized format for strftime/strptime: %Q
InternalError: Invalid Input Error: Failed to parse format specifier : Empty format string
InternalError: Invalid Input Error: strptime format must be a string
InternalError: Binder Error: No function matches the given name and argument types 'try_strptime(DATE, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Parse every row. Rows 1 and 7 match '%Y-%m-%d'; the others are NULL:

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

[(1, '2026-02-28', datetime.datetime(2026, 2, 28, 0, 0)), (2, '01/03/2026', None), (3, 'not a date', None), (4, None, None), (5, '', None), (6, '2026-02-30', None), (7, ' 2026-02-28 ', datetime.datetime(2026, 2, 28, 0, 0))]

Give a list of the forms the data arrives in. Row 2's 01/03/2026 now matches the second format:

run(f"SELECT id, s, try_strptime(s, ['%Y-%m-%d', '%d/%m/%Y']) FROM {TABLE} 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)), (3, 'not a date', None), (4, None, None), (5, '', None), (6, '2026-02-30', None), (7, ' 2026-02-28 ', datetime.datetime(2026, 2, 28, 0, 0))]

Find the rows that have text but don't parse with any of the formats, to fix them at the source:

run(f"SELECT id, s FROM {TABLE} WHERE s IS NOT NULL AND try_strptime(s, ['%Y-%m-%d', '%d/%m/%Y']) IS NULL "
    "ORDER BY id LIMIT 10")

[(3, 'not a date'), (5, ''), (6, '2026-02-30')]

Keep only the rows whose text is a date in 2026 or later:

run(f"SELECT id FROM {TABLE} WHERE try_strptime(s, '%Y-%m-%d') > TIMESTAMP '2026-01-01 00:00:00' ORDER BY id LIMIT 10")

[(1,), (7,)]

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 try_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.
• 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 the last example shows the FILTER step above the scan:

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

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

Make it faster

• 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, such as ts > TIMESTAMP '2026-01-01 00:00:00', is pushed down to VAST DataBase, so rows that don't match never leave storage. A filter on try_strptime(s, ...) parses every row in the Query Engine. The plan of the stored column has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE ts > TIMESTAMP '2026-01-01 00:00:00'")

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

• Parse constants, not columns. A bound written as try_strptime('2026-03-01', '%Y-%m-%d') is computed once; see strptime.

Limitations and errors

• Format errors aren't caught. An unknown code (%s, %F, %Q), an empty format or an empty list fails the query, as with strptime.
• The format must be a constant. A format taken from a column fails with Invalid Input Error: strptime format must be a constant.
• NULL hides the reason. try_strptime doesn't say why a value didn't parse. Run strptime on that value to see the error.
• The same parsing rules as strptime. %f reads a number of microseconds (.5 is 5 microseconds), %Z is ignored, and names must be in English; see strptime.

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

InternalError: Invalid Input Error: strptime format must be a constant
OSError: Date out of range: 2026-2-30

Similar functions

TRY_CAST(s AS DATE) is the shorter choice for text that's already YYYY-MM-DD, and it returns a DATE; use try_strptime for any other form.

run("SELECT try_strptime('2023-02-29', '%Y-%m-%d'), TRY_CAST('2023-02-29' AS DATE), TRY_CAST('29/02/2024' AS DATE), "
    "try_strptime('29/02/2024', '%Y-%m-%d')")
run(f"SELECT id, TRY_CAST(s AS DATE), try_strptime(s, '%Y-%m-%d') FROM {TABLE} ORDER BY id LIMIT 10")

[(None, None, None, None)]
[(1, datetime.date(2026, 2, 28), datetime.datetime(2026, 2, 28, 0, 0)), (2, None, None), (3, None, None), (4, None, None), (5, None, None), (6, None, None), (7, datetime.date(2026, 2, 28), datetime.datetime(2026, 2, 28, 0, 0))]

Clean up:

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

[]
[]

See also

• strptime: the % codes, and the parsing rules.
• Date and time functions: all the date and time functions, and parsing and formatting.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.