Dates and Times in Firebird Queries

Dates and times are one of the worst documented concepts in computer programming ... including database manipulation. Even I can't seem to get it all together.

AM/PM Issue | DateTimeToStr fails | DateTime_to_24hr | UTC

AM/PM Issue

Firebird supports several date/time literals (strings). However, the time part must use the 24 hour format - am and pm cause an error. Both Delphi and Firebird support a now construct

DateTimeToStr fails

This code fragment fails because of how the time is formatted. On my system which produces the standard 12 hour clock format that fails with Firebird.

Notice that LongTimeFormat uses mm for both month and minutes, but that nn always produces minutes. From the Delphi help

Tests indicate that LongTimeFormat is not a system variable, meaning that once you change its value, you do not have to restore it. In other words, changes made by your program only affect your program.

Here are some miscellaneous instructions used to format date/times

So, the basic question is



Time is itself an interesting issue - what with different time zones and daylight savings time - the question becomes This becomes particularly important when These 3 time systems are roughly equivalent According to the Windows SDK help, Windows maintains UTC internally and displays local time based on the user preferences. I suggest placing 3 fields in your tables (only 2 are required, the third can be computed from the other 2)
Warning: In some cases (such as file dates), Windows shows different times for the same value depending on the month of the year (ie, whether or not daylight savings time is in effect).


Author: Robert Clemenzi -
URL: http:// / user / clemenzi / technical / Databases / Delphi / Firebird / Dates.html