Time and Date Notation (SQL)

You can enter the values of DATE, TIME and TIMESTAMP types in SQL using three different ways: the SQL standard, ODBC standard and 602SQL standard. The following table describes the individual methods:

	Date	Time	Date and time
SQL standard	DATE’1962-04-27’	TIME’12:34:56.789’	TIMESTAMP’1962-04-27 12:34:56’
ODBC standard	{d ‘1962-04-27’}	{t ’12:34:56.789’}	{ts ’1962-04-27 12:34:56’}
602SQL standard	27.4.1962	12:34:56.789	27.4.1962 12:34:56

If the time value contains the time zone specification (UTC notation), it is entered as follows:

	Time with a time zone	Date with time and time zone
SQL norm	TIME’12:34:56.789+02:00’	TIMESTAMP’1962-04-27 12:34:56+02:00’
ODBC specification	{t ’12:34:56.789+02:00’}	{ts ’1962-04-27 12:34:56+02:00’}
602SQL style	12:34:56.789+02:00	27.4.1962 12:34:56+02:00

Notes:

Notation according to the ODBC standard in braces is not considered a comment. If you specify a decimal fraction of a second, it is ignored.

The time zone designation must immediately follow after the time notation and begin with a + or - sign.

Variances from the SQL Standard

If the colon and minutes are omitted from the time zone notation 00 will be added.
If you use the 602SQL standard, you can omit the year from the date, the current year will be used.
If you use the 602SQL standard, you can omit the date part or the time part from the timestamp, the current date or time will be used.

Example:

Inserting date, time and timestamp values using the different methods:

procedure InsDate();
BEGIN
  INSERT INTO DateTime(date,time,datetime) 
         VALUES (DATE'1998-02-27',TIME'12:34:56',TIMESTAMP'1998-02-27 12:34:56');
  INSERT INTO DateTime(date,time,datetime)
        VALUES ({d'1998-02-27'},{t'12:34:56.789'},{ts'1998-02-27 12:34:56.789'});
  INSERT INTO DateTime(date,time,datetime) 
         VALUES (27.2.1998,12:34:56,27.2.1998 12:34:56);
END

Character and Binary strings

Lexical Elements of the SQL Language

Comments