Wednesday, May 14, 2008

Working with Date/Time Values

Referencing dates in SQL statements can sometimes be problematic especially when your application is used in many countries. Here in the US we use the MM/DD/YYYY format most of the time. The UK uses DD/MM/YYYY which can cause your SQL statements to fail if you are expecting the US format.

The easiest way around this problem is to use the ANSI date format in your SQL statements (YYYY-MM-DD). Advantage will always respect this format regardless of the format configured on the machine. Advantage will accept the date format defined on the machine and the ANSI format by default.

The date format Advantage uses can be changed using the AdsSetDateFormat which takes a string value for the format. The string must contain two or more occurrences of the letters D (day), M (month) and Y (year). Thus the date could be set with any of the following strings

  • MM/DD/YY
  • DD.MM.YY

Having the date format correct avoids many problems but there are times when you want to get the current date or manipulate the date. Fortunately Advantage has many Date/Time Functions built-in to the SQL Engine. Some of the commonly used ones are below

  • CURDATE() or CURRENT_DATE() returns the current date
  • CURTIME() or CURRENT_TIME([precision]) returns the current time
  • CURRENT_TIMESTAMP([precision]) returns the current date and time
  • TIMESTAMPADD(interval, int, timestamp) increments the timestamp by the specified value for the given interval (i.e. Months, Days, Hours, Seconds)
  • TIMESTAMPDIFF(interval, timestamp1, timestamp2) returns the difference between two timestamp in the specified interval

As a final note with the release of version 8.0 a new field type ModTime was added for ADT tables. This is a timestamp value which is automatically updated whenever a record is updated. It is very useful when auditing changes or as a quick lookup for recently changed items.


Anonymous said...

What is the ansi Date and Time format?

Chris Franz said...

The ANSI/ISO SQL Standard Date format is defined as YYYY-MM-DD by the ANSI 92 SQL standard. It is supported by most database systems including Advantage.
You can purchase the standard from ANSI. I use Guide to SQL Standard, A (4th Edition) when I need information about the standard.

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.