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


DateTime_to_24hr


UTC

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).

References


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