ASA SQL Reference
SQL Data Types
Date and time data types
By default, values stored as DATE do not have any hour or minute values, and so comparison of dates is straightforward.
If you set the TRUNCATE_DATE_VALUES option to OFF, then the DATE data type also contains a time, which introduces complications when comparing dates. If the time is not specified when a date is entered into the database, the time defaults to 0:00 or 12:00am (midnight). Any date comparisons with this option setting compare the times as well as the date itself. A database date value of '1999-05-23 10:00' is not equal to the constant '1999-05-23'. The DATEFORMAT function or one of the other date functions can be used to compare parts of a date and time field. For example,
DATEFORMAT(invoice_date,'yyyy/mm/dd') = '1999/05/23'
If a database column requires only a date, client applications should ensure that times are not specified when data is entered into the database. This way, comparisons with date-only strings will work as expected.
If you wish to compare a date to a string as a string , you must use the DATEFORMAT function or CAST function to convert the date to a string before comparing.