SQL State. Date comparison doesnßt work

[Mischamel]

Hi,

 

I´d like to do an exceutenonquery SQL Statement, that SELECTs ... WHERE FORMAT(Wiedervorlage,'yyyymmdd') <= ' " + WVDate + " ' "

WVDate is formatted in yyyymmdd and is a string.

 

The state. runs without errmsg, but datasets in myselection don´t fit to the <= WVDATE. Their still bigger ones inside.

 

I took this format , cause Formats like yyyy.mm.dd are not comparable, just as integers or strings, correct ???

Or has anybody any suggestion , perhaps , please ??

[pcf108]

You shouldn't compare the two as formatted strings, because that won't give a date comparison. "10" < "2" is true.

 

Would...

 

SELECTs ... WHERE Wiedervorlage <= #" + CDate(WVDate) + "#"

 

Work for you??

[Mischamel]

No !

 

It throws an Err in Date state expression. But there has to be any comfortable way to compare 2 ing Dates. Anything I try fails.

 

CAn someone give advice ??

[Nerseus]

I'd use the Date compare if you can. You'll probably have to format your string date into the proper format. In the US, that format is MM/DD/YYYY. So the SQL should look like:

sql = "SELECT ... WHERE Wiedervorlage <= '" + WVDate.ToString("MM/dd/yyyy") + "'"

That should convert your DateTime variable, WVDate to a string in the format specified. Use single quotes (as shown) for SQL Server, or use "#" for Access. I think Oracle uses # as well. The end SQL should look like:

SELECT ... WHERE Wiedervorlage <= '09/29/2003'

 

If you really want to use the formatted version, you'll want to convert to a number:

sql = "SELECT ... WHERE CONVERT(int, FORMAT(Wiedervorlage, 'yyyymmdd')) <= " + WVDate.ToString("yyyyMMdd")

 

Note that in the above, I didn't put single quotes (or #) around the WVDate value. This is because you'd like to compare two numbers. I've never seen "Format" before - I'm guessing that's Access, but not really sure. If it is Access, change the CONVERT to something like CLng(...) or similar.

 

-Nerseus