SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot Grantspad69's Avatar
    Join Date
    Jun 2003
    Location
    UK
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL - compare two fields with date

    Hi all,

    I have one date field in the format "dd/mm/yyyy" that is actually a URL parameter value and a "fldAppointmentDate" field in a table of an Access 2000 database also in the "dd/mm/yyyy".

    I am using the following ASP code and SQL query but it is not working

    Code:
    <%
    Dim rsAppointments__datevalue
    rsAppointments__datevalue = 01/01/2005
    If (Request.QueryString("dateid") <> "") Then 
    rsAppointments__datevalue = Request.QueryString("dateid")
    End If
    %>
    
    
    - - - - - - - - - SQL QUERY - - - - - - - - -
    
    SELECT * FROM tblAppointments WHERE fldAppointmentDate = + Replace( rsAppointments__datevalue, "'", "''") + " ORDER BY fldAppointmentStartTime ASC"

    Is there a way in the SQL to compare these?

    I am not sure, but I think it maybe because the URL parameter value is a string and the field in the database is a DATETIME. I have played about with the CDate() and FormatDateTime() functions but had no luck.

    I only want to return rows in the database, which have dates that match with the URL parameter value.

    Any suggestions would be greatly appreciated

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rsAppointments__datevalue = 01/01/2005
    Unless it's a number on the number scale, it's gotta be in double-quotes. -But don't quote me on that, hehehe...

    Seriously.. change it to "01/01/2005"

    With Access you can say
    field > 01/01/01 or field > #01/01/01#

    Only SQL Server can you say field > '01/01/01'


    -Dan

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    were the database fields populated with Now() by any chance?

    because if they were, they will have non-zero time components

    otherwise, i can't help you (i don't know whatever language that is you're using)

    oh, and remember, access wants to see its date strings in hash marks

    i.e. after your language has substituted its string values from the url parameter, access wants to see this --

    ... where fldAppointmentDate = #2/8/2005#

    or if the database contains time portions,

    ... where fldAppointmentDate >= #2/8/2005#
    and fldAppointmentDate < #2/9/2005#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •