Filter by Date problem

[Mighty]

I run the following query:

Select PRTNUM_72, LOTNUM_72, ORDNUM_72, TNXDTE_72, TNXQTY_72, CUSTID_72 FROM "Lot Tracking Hist" WHERE TNXDTE_72='2001-11-29' AND TNXCDE_72='S'

This returns no records even though I know that there are records in the table that match the conditions of the query. Can anyone explain this to me. Mise Le Meas,

Mighty

 

[Pervasivite]

First off where are you running this query? Are you sure that TNXDTE_72 is stored in datd format and not timestamp or some other way? What kind of data type is TNXDTE_72? Also are you sure there records where both conditions are met? You could also have bad DDFs.

You can try similar queries with the Demodata database. I used Class to do this query which worked fine.

Select ID, NAME, Max_Size, Start_Date, Start_Time, Room_Number FROM "Class" WHERE Start_Date='1995-06-05' AND Room_Number='101'#
And got these results.

ID Name Max_Size Start_Date Start_Time Room_Number
---------- ------- -------- ---------- ---------- -----------
1 ACC 101 30 1995-06-05 08:00:00 101
97 FRE 102 25 1995-06-05 08:00:00 101
98 FRE 102 25 1995-06-05 11:00:00 101
99 FRE 102 25 1995-06-05 14:00:00 101
204 THE 101 50 1995-06-05 11:00:00 101
205 THE 102 25 1995-06-05 14:00:00 101

(6 row(s) affected)

Regards,
Pervasivite

 

[Mighty]

Pervasivite,

Firstly, thanks for the response. I will try to answer your questions and hopefully you will provide me with some insight.

I am running this query from an ASP program or from a VBScript program using Windows Script Host and connecting to the database using an ODBC connection. Looking at the specs for the database table in question, the data type of the date field in question is BtrvDate. I am absolutely positive that there are records to meet both conditions as I have run the following query and the results displayed records on the date above.

Select PRTNUM_72, LOTNUM_72, ORDNUM_72, TNXDTE_72, TNXQTY_72, CUSTID_72 FROM "Lot Tracking Hist" WHERE CUSTID_72='ABC' AND TNXCDE_72='S'

As regards running queries on the DEMODATA database, I have another query running on a different table in the same database as the one I am having problems with. The query below works fine.

Select PRTNUM_15, TNXDTE_15, ORDNUM_15, TNXQTY_15, TNXTIM_15, UDFKEY_15 FROM &quot;&quot;Transaction History&quot;&quot; WHERE((TNXDTE_15='2001-11-29' AND TNXTIM_15>'030000') OR (TNXDTE_15='2001-11-30' AND TNXTIM_15<='030000')) AND TNXCDE_15='S'

Any insight or tips you can give me would be greatly appreciated. I have been messing with this for ages.
Mise Le Meas,

Mighty