PostgreSQL: to_date Function

In this PostgreSQL post explains how to use the PostgreSQL to_date function with syntax and examples.

Description

The PostgreSQL to_date function converts a string to a date.

Syntax

The syntax for the to_date function in PostgreSQL is:

to_date( string1, format_mask )

Parameters or Arguments

string1

The string that will be converted to a date.

format_mask

The format that will be used to convert string1 to a date. It can be one of the following and can be used in many combinations.

Parameter	Explanation
YYYY	4-digit year
Y,YYY	4-digit year, with comma
YYY YY Y	Last 3, 2, or 1 digit(s) of year
IYYY	4-digit year based on the ISO standard
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month in all uppercase
Mon	Abbreviated name of month capitalized
mon	Abbreviated name of month in all lowercase
MONTH	Name of month in all uppercase, padded with blanks to length of 9 characters
Month	Name of month capitalized, padded with blanks to length of 9 characters
month	Name of month in all lowercase, padded with blanks to length of 9 characters
RM	Month in uppercase Roman numerals
rm	Month in lowercase Roman numerals
WW	Week of year (1-53) where week 1 starts on the first day of the year
W	Week of month (1-5) where week 1 starts on the first day of the month
IW	Week of year (01-53) based on the ISO standard
DAY	Name of day in all uppercase, padded with blanks to length of 9 characters
Day	Name of day capitalized, padded with blanks to length of 9 characters
day	Name of day in all lowercase, padded with blanks to length of 9 characters
DY	Abbreviated name of day in all uppercase
Dy	Abbreviated name of day capitalized
dy	Abbreviated name of day in all lowercase
DDD	Day of year (1-366)
IDDD	Day of year based on ISO year
DD	Day of month (01-31)
D	Day of week (1-7, where 1=Sunday, 7=Saturday)
ID	Day of week based on ISO year (1-7, where 1=Monday, 7=Sunday)
J	Julian day; the number of days since midnight on November 24, 4714 BC
HH	Hour of day (01-12)
HH12	Hour of day (01-12)
HH24	Hour of day (00-23)
MI	Minute (00-59)
SS	Second (00-59)
MS	Millisecond (000-999)
US	Microsecond (000000-999999)
SSSS	Seconds past midnight (0-86399)
am, AM, pm, or PM	Meridian indicator
a.m., A.M., p.m., or P.M.	Meridian indicator
ad, AD, a.d., or A.D	AD indicator
bc, BC, b.c., or B.C.	BC indicator
TZ	Name of time zone in uppercase
tz	Name of time zone in lowercase
CC	2-digit century

Applies To

The to_date function can be used in the following versions of PostgreSQL:

• PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4

Example

Let's look at some PostgreSQL to_date function examples and explore how to use the to_date function in PostgreSQL.

For example:

postgres=# SELECT to_date('2014/04/25', 'YYYY/MM/DD');
  to_date
------------

 2014-04-25
(1 row)

postgres=# SELECT to_date('033114', 'MMDDYY');
  to_date
------------

 2014-03-31
(1 row)

postgres=# SELECT to_date('February 08, 2014', 'Month DD, YYYY');
  to_date
------------

 2014-02-08
(1 row)