By default, values stored as DATE do not have any hour or minute values, and so comparison of dates is straightforward.
The DATE data type can also contain 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 and the date. 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 want 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.
SQL Anywhere uses the following rules when comparing time and date data types. The rules are examined in the order listed, and the first rule that applies is used:
If the data type of either argument is TIME, convert both to TIME and compare.
If either data type has the type DATE or TIMESTAMP, convert to both to TIMESTAMP and compare.
For example, if the two arguments are of type REAL and DATE, they are both converted to TIMESTAMP.
If one argument has NUMERIC data type and the other has FLOAT, convert both to DOUBLE and compare.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|