AcuODBC and Timestamp

[mts176]

I am using AcuODBC to connect to flat files in a accounting program called Sirius.

The query I need uses dates but the only date information available is a timestamp.

How do I search on a timestamp.
This is my current Where clause.
AND (PAY_DATE >= '2004-06-30'
AND PAY_DATE <= '2004-06-1')

But when I run it I get
Type mismatch in WHERE

Can anybody help me out?

 

[RobertT687]

Your dates are reversed.
I think it should read:
AND (PAY_DATE >= '2004-06-1'
AND PAY_DATE <= '2004-06-30')

Could also be written...
AND (PAY_DATE BETWEEN '06-1-2004' AND 06-30-2004')

 

[mts176]

I am still getting the following error
Data Exception 005 : Type mismatch in WHERE
State:22005,Native:8704,Origin:[AcuODBC]

The column in the Query Analyzer says:

PAY_DATE[TIMESTAMP NULL]

Do I have to change the data to varchar, date, time, I can't figure it out. I can't get it to work through my ASP page nor in my query analyzer.

 

[SQLSister]

Timestamp is not a date time field that is why you are getting a mismatch, it does not store dates.

Questions about posting. See faq183-874

 

[mts176]

How do I do a compare on this then. I need to find the data that is listed between certain dates.

I am getting a recordset from SQL Server, I am looping through recordset1 while I am looping through I am quering the Sirius Flat file looking for the expense that coincides with the Date and Vendor of the data I that got from recordset1.

Do I have to now get a recordset from Sirius Flat file, loop through that to check and see if the dates match, or is there some way I can just write a Query to find the expense for a specific Vendor and Date, this way I can just use
if not .eof then

Many of the reports will span many months and have anywhere from 5 to hundreds of expenses that I have to search the flat file for.

 

[SQLSister]

You can't compare dates if the dates are not stored. If the dates are stored they are not in a timestamp column. There is no conversion of a timestamp column to dates that will change the data to date data because the data is not in any way shape or form date oriented. If your original data is in fact date data, then you can store it in SQL Server as a datetime column.

Incidentally you should remove the concept of looping through records from your brain. It is almost always the wrong way to do things.

Questions about posting. See faq183-874