Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

DATEFORMAT and TIMEFORMAT Strings

The DATEFORMAT and TIMEFORMAT options in the COPY command take format strings. These strings can contain datetime separators (such as '-', '/', or ':') and the following "dateparts" and "timeparts".

Note

If the COPY command does not recognize the format of your date or time values, or if your date or time values use different formats, use the 'auto' argument with the TIMEFORMAT parameter. The 'auto' argument recognizes several formats that are not supported when using a DATEFORMAT and TIMEFORMAT string.

Datepart/Timepart Meaning
YY Year without century
YYYY Year with century
MM Month as a number
MON Month as a name (abbreviated name or full name)
DD Day of month as a number
HH or HH24 Hour (24-hour clock)
HH12 Hour (12-hour clock)
MI Minutes
SS Seconds
AM or PM Meridian indicator (for 12-hour clock)

The default timestamp format is YYYY-MM-DD HH:MI:SS, and the default date format is YYYY-MM-DD. The seconds (SS) field also supports fractional seconds up to a microsecond level of detail.

For example, the following DATEFORMAT and TIMEFORMAT strings are valid.

COPY Syntax Example of Valid Input String
DATEFORMAT AS 'MM/DD/YYYY' 03/31/2003
DATEFORMAT AS 'MON DD, YYYY' March 31, 2003
TIMEFORMAT AS 'MM.DD.YYYY HH:MI:SS' 03.31.2003 18:45:05

03.31.2003 18:45:05.123456