timestamp

The following syntax shows the formats for DATE, TIME, and TIMESTAMP constants.

timestamp :
 		TIMESTAMP "YYYY-MM-DD HH:MM[:SS[.dddd]]"

The formats following the DATE, TIME, and TIMESTAMP keywords conform to the SQL standard. In the format for date constants,YYYY is the year (you must specify all four digits), MM is the month number (1 to 12), and DD is the day of the month (1 to 31). The @ symbol represents a nonstandard alternative. When only two digits are specified for the year using the nonstandard format (@), the century is assumed to be 1900 where YY is greater than or equal to 50; where YY is less than 50 in this format, the century is assumed to be 2000.

In the format for time constants, HH is hours (0 to 23), MM is minutes (0 to 59), SS is seconds (0 to 59), and .dddd is the fractional part of a second, with up to four decimal places of accuracy. If you specify more than four places, the value rounds to four places. The format for timestamp constants simply combines the formats for date and time constants.

The DATE format can be adjusted using the SET DATE FORMAT statement.

set_date_format:
          SET DATE FORMAT [TO | =] 
               {"YYYY-MM-DD" | "MM-DD-YYYY" | "DD-MM-YYYY"
            |  "YYYY/MM/DD" | "MM/DD/YYYY" | "DD/MM/YYYY"}

set_date_format:
          SET DATE FORMAT [TO | =] 
               {"YYYY-MM-DD" | "MM-DD-YYYY" | "DD-MM-YYYY"
            |  "YYYY/MM/DD" | "MM/DD/YYYY" | "DD/MM/YYYY"}

The default format conforms to the ISO and SQL standards: YYYY-MM-DD. Two other formats are supported with the option of changing the separator character from a '-' to a '/'.

select * from mytypes where mydob = date "1984-05-08";
update mytypes set mylunch = time "11:45" 
 	and mylastchk = @"2013-02-13 07:58:35";
set date format to "MM/DD/YYYY";
select * from mytypes where mydob > @"01/28/1990";