602SQL Documentation Index  

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


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


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

procedure InsDate();
  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);