PostgreSQL to_date() Function

The PostgreSQL to_date() function converts the specified string to a date value according to the given format.

to_date() Syntax

This is the syntax of the PostgreSQL to_date () function:

to_date(str TEXT, format TEXT) -> TIMESTAMP WITH TIME ZONE

Parameters

str

Required. The string to convert.

format

Required. The format in which to convert this string. You can use the template patterns in the following table to construct format strings.

Pattern	Description
HH	Hour of the day (01–12)
HH12	Hour of the day (01–12)
HH24	Hour of the day (00–23)
MI	Minutes (00–59)
SS	seconds (00–59)
MS	milliseconds (000–999)
US	Microseconds (000000–999999)
FF1	tenths of a second (0–9)
FF2	hundredths of a second (00–99)
FF3	milliseconds (000–999)
FF4	Tenth of a millisecond (0000–9999)
FF5	hundredths of a millisecond (00000–99999)
FF6	Microseconds (000000–999999)
SSSS,SSSSS	Seconds after midnight (0–86399)
AM, am, PM,pm	AM, PM (no period)
A.M., a.m., P.M.,p.m.	AM, PM (with periods)
Y,YYY	Year (4 or more digits) with commas
YYYY	year (4 or more digits)
YYY	Last 3 digits of year
YY	Last 2 digits of year
Y	last digit of year
IYYY	ISO 8601 week number year (4 or more digits)
IYY	Last 3 digits of ISO 8601 week number year
IY	Last 2 digits of ISO 8601 week number year
I	ISO 8601 week number last digit of year
BC, bc, AD,ad	BC, AD (no period)
B.C., b.c., A.D.,a.d.	BC, AD (with periods)
MONTH	All-caps month name (blank padded to 9 characters)
Month	All-caps month name (blank padded to 9 characters)
month	All lowercase month name (blank padded to 9 characters)
MON	Abbreviated uppercase month name (3 characters in English, localized lengths vary)
Mon	Abbreviated uppercase month name (3 characters in English, localized lengths vary)
mon	Abbreviated lowercase month name (3 characters in English, localized lengths vary)
MM	Number of months (01–12)
DAY	Date name in all caps (blank padded to 9 characters)
Day	Date name in all caps (blank padded to 9 characters)
day	All lowercase date name (9 characters padded with blanks)
DY	Abbreviated uppercase date name (3 English characters, localized lengths vary)
Dy	Abbreviated uppercase date name (3 English characters, localized lengths vary)
dy	Abbreviated lowercase date name (3 English characters, localized lengths vary)
DDD	day of the year (001–366)
IDDD	ISO 8601 week number day of the year (001–371; the 1st day of the year is the Monday of the first ISO week)
DD	day of the month (01–31)
D	Day of the week, Sunday ( 1) to Saturday ( 7)
ID	ISO 8601 Day of the week, Monday ( 1) to Sunday ( 7)
W	Week of the month (1–5) (the first week starts on the first day of the month)
WW	Week number of the year (1–53) (the first week starts on the first day of the year)
IW	ISO 8601 week number Week number of the year (01–53; the first Thursday of the year is in week 1)
CC	Century (2 digits) (21st century started on 2001-01-01)
J	Julian calendar date (integer days since local midnight on November 24, 4714 BC)
Q	quarter
RM	Month in uppercase Roman numerals (I to XII; I is January)
rm	Month in lowercase Roman numerals (i to xii; i is January)
TZH	time zone hours
TZM	time zone minutes

You can use the modifiers in the following table with date/time template patterns.

Modifier	Description
FM prefix	fill mode (suppress leading zeroes and padding blanks)
TH suffix	upper case ordinal number suffix
th suffix	lower case ordinal number suffix
FX prefix	fixed format global option (see usage notes)
TM prefix	translation mode (use localized day and month names based on lc_time)
SP suffix	spell mode (not implemented)

Return value

The PostgreSQL to_date() function returns a date value converted from the specified string according to the specified format.

to_date() Examples

This example shows how to use the PostgreSQL to_date() function to convert a specified time string to a date value according to the specified format.

SELECT to_date('2022-05-17', 'YYYY-MM-DD');

  to_date
------------
 2022-05-17