Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Timeless Dates

It's a disappointing fact of life that the Watcom SQL date datatype includes a time component. If this time component is not zero then date comparisons stop working properly. For example,

   select * from acct_txn where posted_date = :adt_posted_date

will not return any rows if the time portion of posted_date column is not zero but the retrieval argument adt_posted_date is a simple date.

This usually happens by accident, as when the Watcom SQL now(*) or DataWindow today() functions are used to initialize a column. Both of these functions return DateTime results whereas the Watcom SQL today(*) and PowerScript today() functions return simple dates.

One way to check for this sorry state of affairs is to execute

   select distinct date_column from table_name

and see if the supposedly distinct values in fact look the same. If so, it's their time components that are different. The cast function can be used to show the time components:

   select cast ( date_column as timestamp ) from table_name

The date function can be used to convert these dates-with-times into true dates:

   select date ( date_column ) from table_name

It can also be used to make a permanent repair:

   update table_name set date_column = date ( date_column )

Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.