TO_DATE function - Amazon Redshift
SyntaxArgumentsReturn typeExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

TO_DATE function

TO_DATE converts a date represented by a character string to a DATE data type.

Syntax

TO_DATE(string, format)
TO_DATE(string, format, is_strict)

Arguments

string

A string to be converted.

format

A string literal that defines the format of the input string, in terms of its date parts. For a list of valid day, month, and year formats, see Datetime format strings.

is_strict

An optional Boolean value that specifies whether an error is returned if an input date value is out of range. When is_strict is set to TRUE, an error is returned if there is an out of range value. When is_strict is set to FALSE, which is the default, then overflow values are accepted.

Return type

TO_DATE returns a DATE, depending on the format value.

If the conversion to format fails, then an error is returned.

Examples

The following SQL statement converts the date 02 Oct 2001 into a date data type.

select to_date('02 Oct 2001', 'DD Mon YYYY');

to_date
------------
2001-10-02
(1 row)

The following SQL statement converts the string 20010631 to a date.

select to_date('20010631', 'YYYYMMDD', FALSE);

The result is July 1, 2001, because there are only 30 days in June.

to_date
------------
2001-07-01

The following SQL statement converts the string 20010631 to a date: 

to_date('20010631', 'YYYYMMDD', TRUE);

The result is an error because there are only 30 days in June.

ERROR:  date/time field date value out of range: 2001-6-31