NextPrevUpTopContentsIndex Working with date fields

To test date fields via Common SQL, compare values by formatting the date as a string, in a date format that the database can parse. For example:

(sql:select * :from [Table] :where [= [Date] "25-Dec-2005"])

Note that it is not possible to lookup date values in the database using numeric values. This is because:

  1. Common SQL cannot know that the field will be a date field until the results are returned, and
  2. the database probably does not know about Common Lisp universal time.

When returning values from fields of DATE types, by default you get a Common Lisp universal time (that is, an integer). You can obtain instead a string formatted by the database by modifying the database identifier, adding :string like this:

(sql:select [MyDate :string] :from [MyTable] :where [= [id] 1])

See the manual page for select in the LispWorks Reference Manual for details.

LispWorks User Guide - 21 Jul 2006