Postgresql To_date Function

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)