DateDiff and varchar dates and "Data Type Mismatch"

[Treefarn]

So I am working with a table that has 2 text fields called DestructionMonth and DestructionYear. The data in DestructionMonth is a 2 character month (01 through 12), and year is a 4 character field.

What I need to do is find all records where the destructiondate is within 60 days/2 months.

I have tried this many different ways and am still getting an error.

So this query works fine:

SELECT documentstable.destructionmonth, documentstable.destructionyear, documentstable.id, Now() AS Expr1, Date() AS Expr2, DateAdd('m',2,destructionyear+"/"+destructionmonth) AS Expr3, Format(destructionyear+"/"+destructionmonth+"/"+"01","Short Date") AS Expr4, CInt(DateDiff("d",Format(destructionyear+"/"+destructionmonth+"/"+"01","Short Date"),Date())) AS MyDateDiff
FROM documentstable


It gives me MyDateDiff in the last column, the difference between the destructiondate and the current date. That works great.

But if I add a where clause with this:
CInt(DateDiff("d",Format(destructionyear+"/"+destructionmonth+"/"+"01","Short Date"),Date())) < 60

I get "Data Type Mismatch in criteria expression".

The value I am comparing to the 60 is an integer! How is it a mismatch?! Removing the CInt (which I had though was unnecessary anyway) doesn't help.

Any thoughts?

 

[spikepl]

You have a confused mess of data types, with strings mixed into this all over.

DateDiff operates on variables of type Date. Forcing it to work on strings, as you are doing, can lead to unpredictable results.

To construct a variable of type Date from month and year, use function DateSerial (look it up in the docs). Use the first of the month for all months. Start with this, and then you have the data to process using DateDiff.

The fiunction Format produces a STRING - do not use it in date operations in SQL,

 

[RainLover]

Can you create a new database with just enough information to demonstrate what you have done.

Please convert to 2003 or 2007.

 

[RainLover]

Spike

I was thinking of saying that. LOL

 

[Treefarn]

Yeah, its a confused mess because when I can't figure something out, I just keep throwing stuff in there, and then clean it up when I finally figure it out.

I tried using the dateserial, but I still have the same 'mismatch' problem.

So this works:

SELECT documentstable.destructionmonth, documentstable.destructionyear, documentstable.id, dateserial(documentstable.destructionyear,documentstable.destructionmonth, "01") as SerialDate, datediff("d",dateserial(documentstable.destructionyear,documentstable.destructionmonth, "01"), date())
FROM documentstable

But this, with the where clause, does not.

SELECT documentstable.destructionmonth, documentstable.destructionyear, documentstable.id, dateserial(documentstable.destructionyear,documentstable.destructionmonth, "01") as SerialDate, datediff("d",dateserial(documentstable.destructionyear,documentstable.destructionmonth, "01"), date())
FROM documentstable
where datediff("d",dateserial(documentstable.destructionyear,documentstable.destructionmonth, "01"), date()) < 60


Oddly enough, if I change the last line < to a + sign, it doesn't fail? Not sure what that where clause is doing. Doesn't give me the results I want, but it does parse. what would this where clause do??

where datediff("d",dateserial(documentstable.destructionyear,documentstable.destructionmonth, "01"), date()) + 60

 

[spikepl]

Arguments of DateSerial are of type integer.

"01" is a string! Take care of your data types.

Remove documentstable. everywhere

you probably have data missing in some records

 

[Treefarn]

Arguments of DateSerial are of type integer.

"01" is a string! Take care of your data types.

Remove documentstable. everywhere

you probably have data missing in some records

But the "01" works just fine when I use the DateSerial in the Select, so that doesn't seem like it should be an issue. Its just when I use the results of DateSerial and try to compare it to >60 that there's a problem.

There is no data missing, there's only 12 records right now, and all are populated.

 

[Treefarn]

you probably have data missing in some records

Ahha, yes, that is indeed it. I simplified the query before I posted it, leaving out parts I didn't think were relevant. There was another piece of a where clause to weed out records that didn't have valid dates in those fields.

Apparently with both the two where clauses, it was validating the datediff first, and failing on the non-valid dates.

Thanks!