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

> ## 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.

# strftime

*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.* Formats a date or timestamp as text, using a format made of `%` codes such as `'%d/%m/%Y %H:%M'`. It's the reverse of [strptime](/develop/docs/dev-database-reference-sql-functions-date-time-strptime).

## Syntax

```
strftime(value, format)
strftime(format, value)
```

| Argument | Type | Description |
| --- | --- | --- |
| `value` | `DATE`, `TIMESTAMP`, `TIMESTAMP_NS`, `TIMESTAMP_MS` or `TIMESTAMP_S` | The date or timestamp to format. |
| `format` | `VARCHAR` constant | The format, made of `%` codes and literal text. |

**Returns:** `VARCHAR`.

The arguments can come in either order: `strftime('%Y-%m-%d', ts)` is the same as `strftime(ts, '%Y-%m-%d')`.

## 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_strftime`. 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 five events. Each has a date (`d`), a timestamp (`ts`) and a timestamp with nanoseconds (`tns`). Row 4 has no values, and row 5 is the last moment of 1969:

```
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_strftime"'

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}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, d DATE, ts TIMESTAMP, tns TIMESTAMP_NS)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-02-28', TIMESTAMP '2026-02-28 23:15:00', CAST('2026-02-28 23:15:00.123456789' AS TIMESTAMP_NS)), "
    "(2, DATE '2026-03-01', TIMESTAMP '2026-03-01 08:30:45.123456', CAST('2026-03-01 08:30:45.123456' AS TIMESTAMP_NS)), "
    "(3, DATE '2024-12-30', TIMESTAMP '2024-12-30 12:00:00', CAST('2024-12-30 12:00:00' AS TIMESTAMP_NS)), "
    "(4, NULL, NULL, NULL), "
    "(5, DATE '1969-12-31', TIMESTAMP '1969-12-31 23:59:59.999999', CAST('1969-12-31 23:59:59.999999999' AS TIMESTAMP_NS))")
```

```
[]
[]
[(5,)]
```

## What it does

`strftime` writes the format out with each `%` code replaced by a part of the value. Any other text is copied as it is, including non-ASCII text. The codes, with the result for `TIMESTAMP &apos;2024-02-29 13:45:30.123456&apos;` (a Thursday):

| Code | Writes | Result |
| --- | --- | --- |
| `%Y`, `%y` | Year; two-digit year | `2024`, `24` |
| `%m`, `%-m` | Month, two digits; without the leading zero | `02`, `2` |
| `%B`, `%b` (also `%h`) | Month name, full and short, in English | `February`, `Feb` |
| `%d`, `%-d` | Day of the month | `29`, `29` |
| `%j`, `%-j` | Day of the year | `060`, `60` |
| `%A`, `%a` | Day name, full and short, in English | `Thursday`, `Thu` |
| `%w`, `%u` | Day of the week: Sunday is `0` with `%w`, `7` with `%u` | `4`, `4` |
| `%U`, `%W` | Week of the year, weeks starting on Sunday or Monday | `08`, `09` |
| `%V`, `%G` | ISO week and ISO year | `09`, `2024` |
| `%H`, `%-H` | Hour, 0 to 23 | `13`, `13` |
| `%I`, `%-I`, `%p` | Hour on the 12-hour clock, and `AM` or `PM` | `01`, `1`, `PM` |
| `%M`, `%S` (also `%-M`, `%-S`) | Minute, second | `45`, `30` |
| `%g`, `%f`, `%n` | Milliseconds, microseconds, nanoseconds (three, six or nine digits) | `123`, `123456`, `123456000` |
| `%z`, `%Z` | UTC offset, and zone name: always `+00` and empty | `+00`, (empty) |
| `%c`, `%x`, `%X` | Date and time, date, time, in ISO form | `2024-02-29 13:45:30`, `2024-02-29`, `13:45:30` |
| `%T` | `%H:%M:%S` | `13:45:30` |
| `%%` | A literal `%` | `%` |

On a `DATE`, the time codes give zeros: `%H:%M:%S` is `00:00:00` and `%p` is `AM`. `%n` gives all nine digits only on a `TIMESTAMP_NS`; a `TIMESTAMP` has microseconds, so `%n` ends in `000`.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| A NULL value or a NULL format | `NULL` |
| `DATE &apos;infinity&apos;`, `TIMESTAMP &apos;-infinity&apos;` | `&apos;infinity&apos;`, `&apos;-infinity&apos;`, whatever the format |
| A year past 9999: `CAST(&apos;10000-01-01&apos; AS DATE)` with `&apos;%Y&apos;` | `&apos;10000&apos;` |
| Midnight and noon with `&apos;%I %p&apos;` | `&apos;12 AM&apos;`, `&apos;12 PM&apos;` |
| A format with no codes: `&apos;no codes&apos;` | `&apos;no codes&apos;` |
| `&apos;%%Y&apos;` | `&apos;%Y&apos;` |
| An empty format | Error: `Invalid Input Error: Failed to parse format specifier : Empty format string` |
| A format that ends in `%` | Error: `Failed to parse format specifier %Y-%m-%d %: Trailing format character %` |
| An unknown code, such as `%Q` | Error: `Failed to parse format specifier %Y-%Q: Unrecognized format for strftime/strptime: %Q` |
| A string as the value: `strftime(&apos;2024-02-29&apos;, &apos;%Y&apos;)` | Error: `Could not choose a best candidate function for the function call &quot;strftime(STRING_LITERAL, STRING_LITERAL)&quot;`; cast the string to `DATE` or `TIMESTAMP` |
| A `TIME`, an `INTERVAL`, a number or a boolean | Error: `No function matches the given name and argument types`, for example `&apos;strftime(TIME, 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-strftime#set-up-the-examples):

```
run(f"SELECT id, strftime(ts, '%Y-%m-%d'), strftime(d, NULL) FROM {TABLE} WHERE id = 4")
run("SELECT strftime(DATE 'infinity', '%Y-%m-%d'), strftime(TIMESTAMP '-infinity', '%Y'), "
    "strftime(CAST('10000-01-01' AS DATE), '%Y'), strftime(TIMESTAMP '2024-02-29 00:00:00', '%I %p'), "
    "strftime(TIMESTAMP '2024-02-29 12:00:00', '%I %p'), strftime(DATE '2024-02-29', 'no codes'), "
    "strftime(DATE '2024-02-29', '%%Y')")
run("SELECT strftime(DATE '2024-02-29', '')")
run("SELECT strftime(DATE '2024-02-29', '%Y-%m-%d %')")
run("SELECT strftime(DATE '2024-02-29', '%Y-%Q')")
run("SELECT strftime('2024-02-29', '%Y')")
run("SELECT strftime(TIME '13:45:30', '%H')")
```

```
[(4, None, None)]
[('infinity', '-infinity', '10000', '12 AM', '12 PM', 'no codes', '%Y')]
InternalError: Invalid Input Error: Failed to parse format specifier : Empty format string
InternalError: Invalid Input Error: Failed to parse format specifier %Y-%m-%d %: Trailing format character %
InternalError: Invalid Input Error: Failed to parse format specifier %Y-%Q: Unrecognized format for strftime/strptime: %Q
InternalError: Binder Error: Could not choose a best candidate function for the function call "strftime(STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'strftime(TIME, STRING_LITERAL)'. You might need to add explicit type casts.
```

## Examples

Each event's timestamp in day-first form, and its date written out:

```
run(f"SELECT id, strftime(ts, '%d/%m/%Y %H:%M:%S.%g'), strftime(d, '%A %d %B %Y') FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, '28/02/2026 23:15:00.000', 'Saturday 28 February 2026'), (2, '01/03/2026 08:30:45.123', 'Sunday 01 March 2026'), (3, '30/12/2024 12:00:00.000', 'Monday 30 December 2024'), (4, None, None), (5, '31/12/1969 23:59:59.999', 'Wednesday 31 December 1969')]
```

The nanoseconds of a `TIMESTAMP_NS` column. Python's `datetime` can't hold them, so formatting the value as text is also a way to read it:

```
run(f"SELECT id, strftime(tns, '%Y-%m-%d %H:%M:%S.%n') FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, '2026-02-28 23:15:00.123456789'), (2, '2026-03-01 08:30:45.123456000'), (3, '2024-12-30 12:00:00.000000000'), (4, None), (5, '1969-12-31 23:59:59.999999999')]
```

Literal text around the codes, with the format first:

```
run("SELECT strftime('Year: %Y, day %j!', TIMESTAMP '2024-02-29 13:45:30'), "
    "strftime(DATE '2024-02-29', 'ann\u00e9e %Y \u00e9 \u65e5')")
```

```
[('Year: 2024, day 060!', 'année 2024 é 日')]
```

Week numbers around the turn of a year. 30 December 2024 is in ISO week 1 of 2025, and 3 January 2027 is in ISO week 53 of 2026:

```
run("SELECT strftime(DATE '2024-12-30', '%U %W %V %G %u %w'), strftime(DATE '2027-01-03', '%U %W %V %G %u %w')")
```

```
[('52 53 01 2025 1 1', '01 00 53 2026 7 0')]
```

## 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 `strftime()` is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine formats the value of each row it returns.
- **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine formats the value and keeps the rows that match. No rows are skipped in storage.

The plan of a filter on the formatted month shows the `FILTER` step above the scan:

```
plan(f"SELECT id FROM {TABLE} WHERE strftime(ts, '%Y-%m') = '2026-02'")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (strftime(ts, '%Y-%m') =  │
│         '2026-02')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Filter on a range of the raw column, not on formatted text.** `ts &gt;= TIMESTAMP &apos;2026-02-01 00:00:00&apos; AND ts &lt; TIMESTAMP &apos;2026-03-01 00:00:00&apos;` returns the same rows as `strftime(ts, &apos;%Y-%m&apos;) = &apos;2026-02&apos;`, and it's pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no `FILTER` step:

```
run(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-02-01 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00' "
    "ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-02-01 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00'")
```

```
[(1,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

- **Format in the `SELECT` list, or in your client.** `strftime` in the `SELECT` list runs only on the rows the query returns. If your client formats dates anyway, you can return the `DATE` or `TIMESTAMP` and format it there.

## Limitations and errors

- **The format must be a constant.** A format taken from a column, or built from one with `||`, fails with `Invalid Input Error: strftime format must be a constant`.
- **Not every `%` code.** `%e`, `%C`, `%D`, `%F`, `%R`, `%r`, `%s`, `%k`, `%l` and others fail with `Unrecognized format for strftime/strptime`. Write `%Y-%m-%d` for `%F`, `%H:%M` for `%R`, and use [epoch](/develop/docs/dev-database-reference-sql-functions-date-time-epoch) for `%s`.
- **No time zones.** A `TIMESTAMP WITH TIME ZONE`, such as `now()`, fails with `No function matches the given name and argument types &apos;strftime(TIMESTAMP WITH TIME ZONE, STRING_LITERAL)&apos;`. Cast it to `TIMESTAMP` first; the result is in UTC, and `%z` gives `+00`. See [time zones](/develop/docs/dev-database-reference-sql-functions-date-time-time-zones).
- **English names only.** `%A`, `%a`, `%B` and `%b` are always in English. For other languages, map the numbers (`%m`, `%u`) to names in your client or with a `CASE` expression.

```
run(f"SELECT strftime(ts, '%Y-' || CAST(id AS VARCHAR)) FROM {TABLE} WHERE id = 1")
run("SELECT strftime(DATE '2024-02-29', '%F')")
run("SELECT strftime(now(), '%Y')")
run("SELECT strftime(CAST(TIMESTAMPTZ '2024-02-29 13:45:30+02' AS TIMESTAMP), '%Y-%m-%d %H:%M %z')")
```

```
InternalError: Invalid Input Error: strftime format must be a constant
InternalError: Invalid Input Error: Failed to parse format specifier %F: Unrecognized format for strftime/strptime: %F
InternalError: Binder Error: No function matches the given name and argument types 'strftime(TIMESTAMP WITH TIME ZONE, STRING_LITERAL)'. You might need to add explicit type casts.
[('2024-02-29 11:45 +00',)]
```

## Similar functions

| Function | What it gives | Result for `TIMESTAMP &apos;2024-12-30 13:45:30.123456&apos;` |
| --- | --- | --- |
| `CAST(ts AS VARCHAR)` | The ISO text of the value, the same as `strftime(ts, &apos;%Y-%m-%d %H:%M:%S.%f&apos;)` here | `&apos;2024-12-30 13:45:30.123456&apos;` |
| [year](/develop/docs/dev-database-reference-sql-functions-date-time-year), [month](/develop/docs/dev-database-reference-sql-functions-date-time-month) and the other parts | The part as a number, not text | `year(...)` is `2024` |
| [dayname](/develop/docs/dev-database-reference-sql-functions-date-time-dayname), [monthname](/develop/docs/dev-database-reference-sql-functions-date-time-monthname) | The same names as `%A` and `%B` | `&apos;Monday&apos;`, `&apos;December&apos;` |
| [date_trunc](/develop/docs/dev-database-reference-sql-functions-date-time-date-trunc) | The start of the month, as a `DATE`, where `strftime(ts, &apos;%Y-%m-01&apos;)` gives text | `2024-12-01` |
| [strptime](/develop/docs/dev-database-reference-sql-functions-date-time-strptime) | The reverse: parses text into a timestamp |  |

```
run("SELECT strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%Y-%m-%d %H:%M:%S.%f'), "
    "CAST(TIMESTAMP '2024-12-30 13:45:30.123456' AS VARCHAR), year(TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%Y')")
run("SELECT dayname(TIMESTAMP '2024-12-30 13:45:30.123456'), monthname(TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%A %B'), date_trunc('month', TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%Y-%m-01')")
```

```
[('2024-12-30 13:45:30.123456', '2024-12-30 13:45:30.123456', 2024, '2024')]
[('Monday', 'December', 'Monday December', datetime.date(2024, 12, 1), '2024-12-01')]
```

Clean up:

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

```
[]
[]
```

## See also

- [strptime](/develop/docs/dev-database-reference-sql-functions-date-time-strptime): parsing text with the same codes.
- [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.