problem with using UK date - any help

[akishore23]

hi, my name is kishore. im using asp with ms access. code is working fine with normal settings i.e US('mm/dd/yyyy') date format.

my actual task is - select records from a table(CPST) between two dates which the user must dates in the format UK('dd/mm/yy'). user will enter dates FromDate and ToDate in Form1.asp. then i carry those two fields with request.form and store in two strings as strFromDate and strToDate.

then i used query - strQueryString="SELECT * FROM CPST where dt >= #" & strFromDate & "# AND dt <= #" & strToDate & "#"

i want to enter data in UK('dd/mm/yy') format and these dates should compare with access database dates whose are in US date format and i want to get records for the specified period and display the date in UK format. since ms access takes date in the US format('mm/dd/yyyy') normally. im not able to get records and display date in UK format in my report. i think u can understand my problem and give the solution. if u dont understand i will explain again.

plz remember - user must enter in 'dd/mm/yy' format only not even in 'dd/mm/yyyy' and store in the table with same format 'dd/mm/yy'. later when i search for records with query i wll get records in display as dd/mm/yy format


kishore

 

[Brianwarnock]

I don't know anything about asp, however ACCESS would have no priblems, it stores dates as a number and is only interested in formating when required, thus although I store dates in uk format if I design a form for input in US format and then use that as the parameters in a select query 01/31/2003 will happily find and display 31/01/2003 in the query.

VBA does make certain assumptions but this is discussed in the FAQ part of this forum I think.

Brian

 

[neileg]

SQL only works with American dates, so if you want to use UK dates you'll have to convert them using Format().

I have found that if you use an alpha numeric format like 01-Jan-2005, then no problems arise, Access understands what is meant anywhere and can use this in SQL.

 

[WIS]

then i used query - strQueryString="SELECT * FROM CPST where dt >= #" & strFromDate & "# AND dt <= #" & strToDate & "#"
kishore

I've had similar trouble - fixed with the Format fn.
strQueryString="SELECT * FROM CPST where dt >= #" & Format(strFromDate,"dd/mmm/yyyy") & "# AND dt <= #" & Format(strToDate,"dd/mmm/yyyy") & "#"

You MUST use mmm. When I 1st did it with "dd/mm/yyyy" it returned American dates if both "dd" and "mm" were 12 or less, eg: 3 July (03/07)returned as 7 Mar (07/03).

 

[Mile-O]

WIS, why are you using the str prefix for dates? That's confusing.

 

[WIS]

WIS, why are you using the str prefix for dates? That's confusing.

You are right. I wanted to show the catch with "mmm" instead of "mm" - so I just copied what had been done and put in the Format fn.

 

[Big2]

you could of just used a date variable though, seems strange to be using string?

But yeah the way to format the date to english is Format(VARIABLE, "dd/mm/yyyy")

as the bloke said above, read into different date formats mmm and yy etc

 

[WIS]

But yeah the way to format the date to english is Format(VARIABLE, "dd/mm/yyyy")

This will give you a wrong answer if both d and m are <= 12. You must use "mmm".

Format(VARIABLE, "dd/mmm/yyyy") or perhaps more correctly, Format(VARIABLE, "dd-mmm-yyyy")

 

[Rich]

strQueryString="SELECT * FROM CPST where dt >= #" & Format(strFromDate,"mm/dd/yy") & "# AND dt <= #" & Format( strToDate, "mm/dd/yy") & "#"