Helpful Information
 
 
Category: MS SQL Development
SQL Comparing dates prob in ASP

Hello! can anyone please help or had similar problems?

I have an Access database with a field named 'bid_close_date' (data type = 'Date/Time' - General Date: DD/MM/YYYY HH:MM:SS) in a table named tblplayers.

I am running the following Query, using NOW() - which I was hoping would return all the players from tblplayers whose bid_close_date is less than NOW().

SQL = "SELECT * FROM tblPlayers WHERE bid_close_date > # " & NOW &" # ;"

HOWEVER! The SQL only seems to be looking at the first part of the Date i.e. DD and not the rest of the date string. i.e. MM/YYYY HH:MM:SS

N.B. when I compare dates in VBscript it recognises the dates and the comparisons works. i.e. return the desired result.

<%
IF Now() > rs("Bid_close_date") THEN

Response.Write("THIS AUCTION CLOSED etc...")

End If
%>

If anyone has any suggestions I would be most grateful.

David

Try asking the SQL Server to compare it's local version of the current date/time, instead of passing it yourself. In MS SQL Server, the current date/time can be gotten with the getdate() function. Not sure what your would be but I'm sure there is an equivalent. It's safer too, since you only have to worry about keeping the SQL Server's date/time correct. Here's what it would be in MS SQL Server:
SELECT * FROM tblPlayers WHERE bid_close_date > getdate()
-Dave










privacy (GDPR)