TO_YYYYMMDD

Extracts year, month and day from a DATE, TIMESTAMP, or TIMESTAMPTZ value and combines them into an integer beginning with the four-digit year followed by the two-digit month followed by the two-digit day. TO_YYYYMMDD(<expression>) is equivalent to EXTRACT(YEAR FROM <expression>) * 10000 + EXTRACT(MONTH FROM <expression>) * 100 + EXTRACT(DAY FROM <expression>);

Syntax

TO_YYYYMMDD(<expression>)

Parameters

Parameter	Description	Supported input types
`<expression>`	The expression from which the time units are extracted.	`DATE`, `TIMESTAMP`, `TIMESTAMPTZ`

TIMESTAMPTZ values are converted to local time according to the session’s time_zone setting before extracting the time units.

Return Types

INT

Remarks

The TO_YYYYMMDD function can be used in the PARTITION BY clause of CREATE TABLE commands.

CREATE TABLE test (
  t TIMESTAMP
)
PARTITION BY TO_YYYYMMDD(t);

Example

SELECT TO_YYYYMMDD('2025-04-03') as yyyymmdd, TO_YYYYMM('2025-04-03') as yyyymm;

yyyymmdd int	yyyymm int
20250403	202504

Rows: 1Execution time: 5.16ms

SELECT TO_YYYYMMDD('1920-12-30') as yyyymmdd, TO_YYYYMM('1920-12-30') as yyyymm;

yyyymmdd int	yyyymm int
19201230	192012

Rows: 1Execution time: 4.86ms

TO_YYYYMM

ST_ASBINARY

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Types

Remarks

Example

​Syntax

​Parameters

​Return Types

​Remarks

​Example

Syntax

Parameters

Return Types

Remarks

Example