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!

Is fld_date a field with a Data Type of Date/Time?

Yes, type is Date/Time

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.

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
HTH
..incidentally you may be better off asking ASP questions in the ASP forum, rather than the Access forum... just a thought

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.

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?

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!

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%>

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%>