Date and time constants

The following table describes the date and time constants.

Table 1. Date and time constants
Notation	Description
MM	One-digit or two-digit month
DD	One-digit or two-digit day
YY	Two-digit year
YYYY	Four-digit year
MON	Three-letter month (JAN, FEB, MAR)

You can use the following date formats:

MM-DD-YY
MM-DD-YYYY
MM/DD/YY
MM/DD/YYYY
MM.DD.YY
MM.DD.YYYY
YY-MM-DD
YYYY-MM-DD
YY/MM/DD
YYYY/MM/DD
YY.MM.DD
YYYY.MM.DD
YYYYMMDD
YYYY.DDD (year and day number in year)
DD-MON-YY

For date values, it is assumed that the first value MM represents the number of the month. However, if the MM value is greater than 12, Netezza Performance Server treats the first value as the day of month (DD) and treats the next value as MM. For example, 01/07/2007 is January 7, 2007, but 14/07/2007 is treated as July 14, 2007. Use consistent date formats within your queries.

You can use three-character month abbreviations or full month names, for example:

July 4, 1776
Jul 4, 1776
4 July 1776
4 Jul 1776

If you specify a two-digit year of YY, the system resolves the year to a value in the range from 1970 to 2069. For example, select cast('21-3-01' as date); returns a value of 2001-03-21, while select cast('21-3-69' as date); returns a value of 2069-03-21 and select cast('21-3-70' as date); returns a value of 1970-03-21.

You can specify time as one-digit or two-digit hours and minutes with optional one-digit or two-digit seconds (and seconds can include 0 - 6 digits after the decimal point). For example, 01:30:45 or 12:14:66.123456

You can use optional am/AM or pm/PM suffixes. If you omit the suffix, Netezza Performance Server SQL assumes a 24-hour notation (also called military time).

You specify time zones as signed hours with optional minutes; for example -HH or +HH:MM, which indicate the offset of the local time zone from GMT. For example, EST is -05 hours from GMT. The minus sign means west of Greenwich. The range of time zones is -12:59 to +13:00.