Post reply

How to use Dates in SQL Criteria

  • March 5, 2005 at 2:47 pm

    #66513

    I am having problems figuring out how to use a date expressiopn in a criteria string. I am running this on an ASP page using an ADO recordset.

    I have tried this, it returns 0 rows.

    sql = "SELECT Count(tblLinkRequest.LinkRequestID) AS CountOfLinkRequestID, tblLinkRequest.DateSent FROM tblLinkRequest GROUP BY tblLinkRequest.DateSent HAVING (((tblLinkRequest.DateSent)=" & Date() & "));"

    And someone suggested this;

    @sql = 'SELECT Count(tblLinkRequest.LinkRequestID) AS CountOfLinkRequestID, tblLinkRequest.DateSent FROM tblLinkRequest GROUP BY tblLinkRequest.DateSent HAVING (((CONVERT( varchar, tblLinkRequest.DateSent, 101))=' + CHAR(39) +  CONVERT( varchar, GETDATE(), 101) + CHAR(39) + '))'

    But how to convert this to something that can run on an asp page? I replaced the single quotes with double quotes, but now I get this error;

    sql = "SELECT Count(tblLinkRequest.LinkRequestID) AS CountOfLinkRequestID, tblLinkRequest.DateSent FROM tblLinkRequest GROUP BY tblLinkRequest.DateSent HAVING (((CONVERT( varchar, tblLinkRequest.DateSent, 101))=" & CHAR(39) & CONVERT( varchar, DATE(), 101) & CHAR(39) & "))"

    Error Type:

    Microsoft VBScript runtime (0x800A000D)

    Type mismatch: 'CHAR'

    SO....... the date is stored in the databasae in this format;

    1/8/2005

    And the "Response.Write Date()" returns 3/5/2005

    So they are in the same format, how come I can't return any rows?

    Puzzled.......

    Kindest Regards,

    Jon
    Free Cell Phone Games

  • March 6, 2005 at 8:53 am

    #545630

    If this is for a Jet database, try wrapping the date in # signs, ie:

    "HAVING (((tblLinkRequest.DateSent=#" & Date() & "#)));"

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply