Testing Date Fields

I have an Access database which contains, a Date field that I need to test  
against in order to delete a record.
Yet, when I try something like the following:

(setf FooTestDate (encode-universal-time 0 0 0 4 7 2008 0))

(delete-records :from [FooTable] :where [= [FooDate] FooTestDate])

It closes without incident but it doesn't delete the record. When I read  
the value of the Date field it returns something like ("2008-07-04  
00:00:00,,0") So I am wondering if the timezone or the daylight savings  
time may be the issue.

What am I doing wrong?

-- 

Thanks,

William

Re: Testing Date Fields

Hello William,

| I have an Access database which contains, a Date field that I need to
| test  against in order to delete a record.
| Yet, when I try something like the following:
|
| (setf FooTestDate (encode-universal-time 0 0 0 4 7 2008 0))
|
| (delete-records :from [FooTable] :where [= [FooDate] FooTestDate])
|
| It closes without incident but it doesn't delete the record. When I
| read  the value of the Date field it returns something like
| ("2008-07-04  00:00:00,,0") So I am wondering if the timezone or the
daylight
| savings  time may be the issue.

I am not sure about CommonSQL subtlety, but try to pass a string instead of
integer value of universal time, e.g.

(setf FooTestDate "2008-07-04") and so on.

In YSQL equipped with the local-time package, you could write something like

(setf FooTestDate @2008-07-04)
--
Sincerely,
Dmitriy Ivanov
lisp.ystok.ru

Re: Testing Date Fields

"William Proffitt" <Jacobite1607@gmail.com> writes:

> I have an Access database which contains, a Date field that I need to
> test  against in order to delete a record.
> Yet, when I try something like the following:
>
> (setf FooTestDate (encode-universal-time 0 0 0 4 7 2008 0))
>
> (delete-records :from [FooTable] :where [= [FooDate] FooTestDate])
>
> It closes without incident but it doesn't delete the record. When I
> read  the value of the Date field it returns something like
> ("2008-07-04  00:00:00,,0") So I am wondering if the timezone or the
> daylight savings  time may be the issue.
>
> What am I doing wrong?

Hi

I had a similar issue with dates in CLSQL and PostgreSQL 2 years ago (I have not used it again so this may have changed). IIRC, there seemed to be no automatic conversion between CL dates and Postgres dates. I used the SQL database's functions to get dates and then used those results in my other queries, etc. E.g.

(defun sql-date (year month day &key (database *default-database*))
  (car (query (format nil "SELECT to_date('~A-~A-~A', 'YYYY-MM-DD')" year month day) :flatp t :database database)))

(setf FooTestDate (sql-date 2008 7 4))

(delete-records :from [FooTable] :where [= [FooDate] FooTestDate])

Where "to_date" is a PostgreSQL function. Don't Access have similar functions? This way, if there is no automatic date conversion, you are at least sure the format will be correct if strings are required, and you'll be in range if integers are expected.

HTH
Nico