Amazon Redshift
Database Developer Guide (API Version 2012-12-01)
Did this page help you?  Yes | No |  Tell us about it...
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.

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 timestamp 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:

select dateadd(day,1,'today');

date_add
---------------------
2009-11-17 00:00:00
(1 row)

select dateadd(day,1,'now');

date_add
----------------------------
2009-11-17 10:45:32.021394
(1 row)