TO_DATE function - Amazon Redshift

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