Date < Now ?

[eL_NiNo]

What's wrong with this code ?
When used in PWS, it works ok, but on IIS, it displays all the date greater than now.


rs.Source = "SELECT * FROM Information WHERE [Return date] < '" & Now & "'"


I also tried this, w/o success :


rs.Source = "SELECT * FROM Information WHERE [Return date] < #" & Now & "#"

[JoshT]

Check the clock on your server. The SQL should be totally independant of whether you're running PWS or IIS.

[eL_NiNo]

Well PWS is installed on a french Win98 System : The dateformat is : 2001/07/05 while IIS run on an english 2000 server where dateformat is 5/7/2001.

I tried reformatting the strings manually, but stills no go.
Any idea what might me causing this issue ?
I tried that and no go, to my surprise :


rs.Source = "SELECT * FROM Information WHERE [Return date] < #" & "2001/07/05" & "#"



Edited for spelling mistakes :D

[monte96]

You have to get the dates in the same format before you try to compare them. I've run into this sort of issue before..

[harsoni]

Hi,
When you say now it returns you date and the time. So take the date part alone by formatting and then pass that to the sql query. Hope that will work for you.

Sonia:rolleyes:

[eL_NiNo]

That's why I posted this code earlier :
It doesn't work neither, although dates are stored identically in db !



rs.Source = "SELECT * FROM Information WHERE [Return date] < #" & "2001/07/05" & "#"

[eL_NiNo]

I just noticed that the db stored the date as a string. I cannot change this due to the large amount of data in it and I don't want to be held responsible for possible loss of data. Could this have an impact on the comparison ? If so, how can I accomplish this ?

[BOUND4DOOM]

Actually you are better off using DateDiff

I have never seen a < or > or = for that matter work with dates

Try This


IntegerVariable = DateDiff("d", CDate(FirstDate), Date())


if the FirstDate is < Then it returns a Negative Number if it is greater it returns possitive, if it is the same day then it is =

[BOUND4DOOM]

Ack Never Mind me, you were after a SQL Statement

[eL_NiNo]

Aynone ?

[JoshT]

What Db format is it. I know SQL Server has CAST and CONVERT functions you could use to go from string to date -- one of them is part of ANSI SQL, the other MS T-SQL, can't remember which is which.

[eL_NiNo]

It is an Access DB.

[KumarGovind]

just check the dateformat of the [return date] and see that both the format is same if not same just convert to the saem format or u can use datediff(d,getdate(),[return date])


pls. reply if this works out

kumarg
kumargovind@yahoo.com

[fotinan]

Now is a keyword and not a variable so you do not need '" & now & "'
try just now
hope this helps.