#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    7
    Rep Power
    0

    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
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    New York
    Posts
    49
    Rep Power
    12
    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

IMN logo majestic logo threadwatch logo seochat tools logo