Access and Cicode Date/Time Conversions

Access and Cicode have different Date/Time variables data types. Date references in an external database has to be based on the Gregorian Calendar.

You can convert between date and time in three ways:

• Convert to real numbers - In both Cicode and Access you can equate real numbers to data/time variables, like this:

AccessTime = 25568.66667 + (CicodeTime/86400);
CitectTime = 86400*(AccessTime - 25568.66667);

• Convert to strings - The Data and/or Time are converted to and from text strings using the standard conversion functions available in each environment.
• Use the #Date/Time# SQL syntax - The Jet Engine will convert and Dates and Time strings enclosed in # markers. This date could be useful in a WHERE clause:

SELECT * FROM qryMyQuery WHERE 'Date' BETWEEN #3/20/96# AND #3/27/96#

The American Date format is used in this case, the Jet Engine DLL ignores the local Date and Time settings as set in Windows Control Panel.

Published June 2018