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