PostgreSQL: to_date Function
This PostgreSQL tutorial 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
YLast 3, 2, or 1 digit(s) of year IYYY 4-digit year based on the ISO standard IYY
IY
ILast 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)
Advertisements