Date and Timestamp Literals

Dates

The following input dates are all valid examples of literal date values
that you can load into Amazon Redshift tables. The default MDY DateStyle mode is
assumed to be in effect, which means that the month value precedes the day
value in strings such as 1999-01-08 and 01/02/00.

Note

A date or time stamp literal must be enclosed in quotes when you load
it into a table.

Input date

Full Date

January 8, 1999

January 8, 1999

1999-01-08

January 8, 1999

1/8/1999

January 8, 1999

01/02/00

January 2, 2000

2000-Jan-31

January 31, 2000

Jan-31-2000

January 31, 2000

31-Jan-2000

January 31, 2000

20080215

February 15, 2008

080215

February 15, 2008

2008.366

December 31, 2008 (3-digit part of date must
be between 001 and 366)

Timestamps

The following input timestamps are all valid examples of literal time
values that you can load into Amazon Redshift tables. All of the valid date
literals can be combined with the following time literals.

Input Timestamps (Concatenated Dates and
Times)

Description (of Time Part)

20080215 04:05:06.789

4:05 am and 6.789 seconds

20080215 04:05:06

4:05 am and 6 seconds

20080215 04:05

4:05 am exactly

20080215 040506

4:05 am and 6 seconds

20080215 04:05 AM

4:05 am exactly; AM is optional

20080215 04:05 PM

4:05 pm exactly; hour value must be < 12.

20080215 16:05

4:05 05 pm exactly

20080215

Midnight (by default)

Special
Datetime Values

The following special values can be used as datetime literals and as
arguments to date functions. They require single quotes and are converted to
regular timestamp values during query processing.

Description

now

Evaluates to the start time of the current
transaction and returns a timestamp with microsecond precision.

today

Evaluates to the appropriate date and returns
a timestamp with zeroes for the timeparts.

tomorrow

yesterday

The following examples show how now and today
work in conjunction with the DATEADD function: