Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: Access/SQL Date Leading Zeros Issue

    Hi all,

    Right... I've been banging my head against a brick wall on this one. I'm using an access database as the database for an ASP VBscript website and using SQL to insert, edit and retrieve the data.

    So... to the problem.

    I have a date field that, when included as part of the SELECT statement (ie. "SELECT * FROM table WHERE fld_date = #15/04/2009#") the database fails to find any results. If any part of the date has a leading zero (eg. 01/04/2009, 01/10/2009, 28/04/2009) no results are found.

    Bear the following in mind:
    - The date is in UK format (dd/mm/yyyy)
    - Yes, there definitely should be results... I can change the date of a record to suit my fault finding date and it works find on any date from the 10th onwards in October, November and December.
    - If I try to export to .csv or similar format to see what values are actually in the table there are no leading zeros shown... unless I tick the export with leading zeros box!

    There is obviously some leading zero issue here in MS Access which I can't seem to resolve. I've tried using a format mask of dd/mm/yyyy to try to force a leading zero, but with no luck.

    Any ideas?


    I'd be very appreciative of any suggestions anyone has!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is fld_date a field with a Data Type of Date/Time?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2009
    Posts
    5
    Quote Originally Posted by StarTrekker
    Is fld_date a field with a Data Type of Date/Time?
    Yes, type is Date/Time

  4. #4
    Join Date
    Apr 2009
    Posts
    5
    Also, I've just remembered that if I structure the SQL query with a date with no leading zeros (eg. WHERE fld_date = #1/4/2009#) it still won't find any records with a date of 1/4/2009.

    Very strange. Very, very strange.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when talking to JET databases you have to specify the date as US mm/dd/yy
    you may beable to use the ISO form instead eg yyyy/mm/dd.

    how has the date been specified, is it possible that there is a time element stored in fld_date.
    ie is the date set by users or by code, if its by code have you used the appropriate fucntion. I dunno what ASP uses but in VBA you can use DATE() or NOW() to set a date (the gotcha is that NOW() also sets a time). when you try to compare a specified date to a datecolumn in JET the tiome won't match. to prove or disprove if thats a problem try running
    Code:
    select format(fld_date,LongDate) from mytable
    HTH
    ..incidentally you may be better off asking ASP questions in the ASP forum, rather than the Access forum... just a thought
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2009
    Posts
    5
    I've been using date() to for the date, or built it from data input on a form, literally by taking a day field, month field and year field and seperating them with a /

    I did wonder about the time element last night, but that doesn't seem to hold up as once you start using dates with no leading zeros (eg 11th October) it all works as expected.

    How do you get a web server that got a UK locale to produce US format dates when calling functions like Date(), Now(), etc?

    I don't believe this is an ASP issue, but rather an Access Database issue as it doesn't seem to matter how I format the date in either the SQL query or when inserting it in the DB in the first place.
    Last edited by andy_sq; 04-16-09 at 06:23.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you don't need to worry about the date locale, as the date function should return the numeric value of the date (and time). JET stores dates as numbers (IIRC the number of days since 01/01/1900, the decimal part is the time (ie .25 = 06:00, .75 = 18:00 and so on)

    it shouldn't matter how you insert the date, providing you stick by the requisite rules for the db you are using.. JET handles dates happily as either US or ISO.

    This may well be a JET database issue (there is no such thing as an Access database issue, Access is a development tool not a database), but its a problem from ASP, so in my books you are more likely to get an effective answer by asking this in the ASP forum... do you want the question moving there?
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2009
    Posts
    5
    If JET looks to be the issue, then the ASP forum is probably the best place.

    Thanks for your help so far. Fingers crossed someone in the ASP forum can shed some light on this!

    I do have an alternative option up my sleeve, which is to turn the date into a kind of ISO format (yyyymmdd) and store it as a number. That way I can still look between date ranges and sort by date increasing. It'll always be an 8 digit number, and the first digit can never be 0 so it can't drop any leading zeros.

    I'd still love to get to the bottom of why there's this problem with leading zeros in dates though!
    Last edited by andy_sq; 04-16-09 at 08:47.

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    how is the server setup is it uk format or us format that make a big differance

    or you can set the page to the right format


    for a safe beat I always write my querys so that the date is in USA


    [CODE]
    function USAD(date)
    USAD = month(date) &"\" & day(date) & "\" & Year(date)
    End Function
    [\CODE]

    so the code would look some like

    SQL = "SELECT bla.* FROM BLA WHERE MYDATE = #" & USAD(ThisDATE) & "#"

    when displaying the data back to the Screen HTML

    at the top of the page put this
    <%session.lcid=2057%>
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    how is the server setup is it uk format or us format that make a big differance

    or you can set the page to the right format


    for a safe beat I always write my querys so that the date is in USA


    [CODE]
    function USAD(date)
    USAD = month(date) &"\" & day(date) & "\" & Year(date)
    End Function
    [\CODE]

    so the code would look some like

    SQL = "SELECT bla.* FROM BLA WHERE MYDATE = #" & USAD(ThisDATE) & "#"

    when displaying the data back to the Screen HTML

    at the top of the page put this
    <%session.lcid=2057%>
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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