1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help - about SELECT by date in Access

Discussion in 'C#' started by mongkb, Aug 14, 2006.

  1. #1
    Help - about SELECT by date in Access

    Hi all, I've created a table in Access and I assigned the date format to a field , named 'bdate', to "dd/mm/yyyy", the 'bdate' records are:

    23/07/2006
    02/08/2006
    15/08/2006

    Here is the ASP Code:
    
    <%session.lcid=2057%>
    <%
    selDate = FormatDateTime(selDate, 2)
    strSQL = "SELECT * FROM tblList WHERE bdate = #" & selDate & "#"
    Set rs = ObjConn.Execute(strSQL)
    %>
    
    Code (markup):
    Problem:
    This SQL works fine with record no.1 and 3, but select no.2 will result EOF, but if I change the selDate to 08/02/2006, the record no.2 will be found.
    SEMrush
    I've made a debug page here, it clearly shows the detail of the issue:
    http://www.mong.hk/test/c.asp

    Can someone help me to solve this problem, thank you very much for your time and sorry for my poor English.
     
    mongkb, Aug 14, 2006 IP
    SEMrush
  2. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #2
    FormatDateTime(Date(),2) returns date in mm/dd/yyyy

    So you have to convert your date to dd/mm/yyyy

    temp_array = split(seldate, "/")
    seldateUSFormat = temp_array(1) & "/" & temp_array(0) & "/" & temp_array(2)

    strSQL = "SELECT * FROM tblList WHERE bdate = #" & seldateUSFormat & "#"
     
    fluid, Aug 15, 2006 IP
  3. Free Born John

    Free Born John Guest

    Messages:
    111
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #3
    hi,

    if you use dd-mmm-yyyy (e.g. 11-mar-2006) it will work for any international setting
     
    Free Born John, Aug 15, 2006 IP
  4. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #4
    access dates always seem so backwards to me. i work in mysql and as/400 which both use the yyyy-mm-dd convention - i've kinda gotten to where i prefer it that way.

    if it's a true date field - couldn't you just convert the string to date and bounce it off of the db using cdate()? correct me if i'm wrong - it's been forever since i worked with access and dates.

    Fluid has a nice suggestion - and a very interesting idea about splitting the date into an array like that. more elegant than i would have come up with - kudos fluid :D

    vectorgraphx
     
    vectorgraphx, Aug 15, 2006 IP
  5. mongkb

    mongkb Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    The problem has been fixed~ :)
    Thank you very much to all of you guys.
     
    mongkb, Aug 16, 2006 IP