Paradox DB and date queries

[TBreak]

Can anyone explain why the following query returns an empty dataset, even though there are definitely records which match...

SELECT * FROM TRANSACTIONS.DB WHERE "Submit Date" BETWEEN "01/01/2003" AND "31/12/2003"

and:

SELECT * FROM TRANSACTIONS.DB WHERE &quot;Submit Date&quot;>=&quot;01/01/2003&quot; AND &quot;Submit Date&quot;<=&quot;31/12/2003&quot;

and the same as above but with US date format and mmddyy format.

The BDE settings are 4 digit year and leading zero's on month & day.

Thanks if you can help.

 

[LucieLastic]

hi

Also, see if this works:

SELECT * FROM TRANSACTIONS.DB WHERE &quot;Submit Date&quot;>=&quot;2003/01/01&quot; AND &quot;Submit Date&quot;<=&quot;2003/12/31&quot;



lou

 

[TBreak]

Thx lou,

As I mentioned I have tried US and other formats with the same query and no luck. I have also tried using # around the date but that resulted in an exception of invalid use of token

Anyone else any ideas?

TIA

 

[LucieLastic]

So, you've definitely tried with the year at the beginning? as I know in MS SQLServer, yyyy/mm/dd tends to get round any formatting problems.

What about including the time with your date?

Obvious question this but your field is declared as a TDateTime in the database? I've known people in the past to declare them as strings /

If all else fails, then I don't know why, sorry.

lou

 

[TBreak]

Yup, I have tried all formats, yyyy/mm/dd, dd/mm/yyyy etc... and used dash seperators as well as slashes. The fields are declared as Dates (D), NOT DateTime (@).

Anyone else any ideas why such a simple query won't work?

 

[towerbase]

Paradox requires dates to be in 'mm/dd/yyy' format. I'm using Delphi 5. The BDE is version 5.01.

This SQL query works for me to select records (of photographs) taken between 1st March and 1st April this year. It works in both DB Explorer and also in a compiled Delphi test application.

select * from album
where taken between '03/01/2003' and '04/01/2003'

Andrew

 

[TBreak]

Thanks Andrew, I tested that and it was certainly a major part of the problem. The other being the fact that the DB I am accessing has SPACES in the field names and SQL doesn't seem to like that, even if it is in &quot;&quot;

Wierd, but true. So I am now off to get a few drinks and forget about all this until next week.

Thanks again everyone!