September 1st, 2003, 07:53 AM
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...")
If anyone has any suggestions I would be most grateful.
September 1st, 2003, 03:39 PM
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()