TurboSQL Guide

Timestamp Formats

Previous  Top  Next

Timestamp values can be indicated either in the TurboDB proprietary format which does not require quotation marks (dd.mm.yyyy_hh:mm:ss) or in three different standard timestamp formats. Where local date formats are allowed, only the TurboDB format and the current local format are valid. In these situations the quoted standard timestamp formats are not accepted.

The native format for timestamp literals is composed of a date literal and a time literal separated by an underscore '_'. This format is used without quotes:

WHERE StartTime >= 31.1.2001_14:10:00.500

This format is always valid and always interpreted in the same way. You should prefer it wherever you do not want the timestamp format to adjust to the local settings on the computer.

The other way to specify a timestamp is to proceed it by the keyword TIMESTAMP and enclose it in single quotes. Again, we have three different representations here:

The American timestamp format:

WHERE StartTime >= TIMESTAMP'1/31/2001 2:10:00 pm'

The international timestamp format:

WHERE StartTime >= TIMESTAMP'2001-1-31 14:10:00'

The European timestamp format:

WHERE StartTime >= TIMESTAMP'31.1.2001 14:10:00'

When the nature of the string is obvious like in the above samples, you can omit the keyword TIMESTAMP.

When you want to use the native format without enclosing quotes with the Delphi/C++ Builder component TTdbQuery, it will create a parameter because the VCL parser for SQL commands recognizes the colon as the starting character of a parameter. You can either delete it or ignore it, the statement will be executed correctly anyway.