TO_YYYYMM

Reference material for the TO_YYYYMM function

Extracts the year and month 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. TO_YYYYMM(<expression>) is equivalent to EXTRACT(YEAR FROM <expression>) * 100 + EXTRACT(MONTH FROM <expression>);

Syntax

TO_YYYYMM(<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_YYYYMM function can be used in the PARTITION BY clause of CREATE TABLE commands.

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

Example

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

yyyymm int	yyyymmdd int
202504	20250403

Rows: 1Execution time: 4.78ms

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

yyyymm int	yyyymmdd int
192012	19201230

Rows: 1Execution time: 5.94ms