Access Dates and ASP

  • Thread starter Thread starter rp
  • Start date Start date
R

rp

Hi,

I wonder if anyone can help me explain and maybe fix some issues I'm having
with dates and Access 2000 and XP.

I have an asp web application which has been developed on a w2k server on
our intranet, the format of the dates have always caused problems but I
thought I'd coded it to work ok.

I now need to demo the software so I have grabbed the code across onto my
laptop (XP SP1) and created a new web directory and set it all up in IIS to
run standalone. The application is written in ASP 3.0 with an access
database.

On one particular screen a user enters in a start and end date (format
dd/mm/yy) on a form and that is then processed on the asp page. Firstly I
run a piece of SQL which checks to make sure no other entries cross over on
the dates given. Here I have to pass the data in the format MM/DD/YY,
access just wont accept anything else. Later I open a record set, create a
new record and pass the relevant values. Two of which are the start and end
dates, here I have to pass the dates in DD/MM/YY format? This all a occurs
on the same asp page on the same laptop. Why is it like this??

Thanks,

Rick
 
Access expects dates in SQL queries to be in mm/dd/yyyy (or some unambigous
format like dd-mmm-yyyy or yyyy-mm-dd). It does not respect locale
information.

The best thing to do is explicitly format the dates in all SQL statements.
The best approach when using Access is what's shown in
http://www.mvps.org/access/datetime/date0005.htm at "The Access Web".
Unfortunately (unless there have been changes since the last time I did any
ASP coding), the Format function doesn't exist in VBScript, so you'll have
to write your own equivalent.

Here's what I last used:

Function FormatDate(DateToFormat)
' Given a date, this function formats it in yyyy/mm/dd format
' If DateToFormat isn't a valid date, the function returns DateToFormat
Dim ConvertedDate

If IsDate(DateToFormat) Then
ConvertedDate = CDate(DateToFormat)
FormatDate = "#" & Year(ConvertedDate) & "/"
FormatDate = FormatDate & AddPrecedingZero(Month(ConvertedDate)) & "/"
FormatDate = FormatDate & AddPrecedingZero(Day(ConvertedDate)) & "#"
Else
FormatDate = DateToFormat
End If

End Function

Function FormatDateAndTime(DateToFormat)
' Given a date, this function format it in yyyy/mm/dd hh:nn format
' If DateToFormat isn't a valid date, the function returns DateToFormat

Dim ConvertedDate
Dim strWorking

If IsDate(DateToFormat) Then
ConvertedDate = CDate(DateToFormat)
strWorking = "#" & AddPrecedingZero(Year(ConvertedDate)) & "/"
strWorking = strWorking & AddPrecedingZero(Month(ConvertedDate)) & "/"
strWorking = strWorking & AddPrecedingZero(Day(ConvertedDate)) & " "
strWorking = strWorking & AddPrecedingZero(Hour(ConvertedDate)) & ":"
strWorking = strWorking & AddPrecedingZero(Minute(ConvertedDate)) & "#"
FormatDateAndTime = strWorking
Else
FormatDateAndTime = DateToFormat
End If

End Function

Function AddPrecedingZero(theNumber)
' This function accepts a number and puts a leading zero if it's
' less than 10.

If theNumber < 10 Then
AddPrecedingZero = "0" & theNumber
Else
AddPrecedingZero = theNumber
End If

End Function


Note that CDate DOES respect locale information. If the Regional Settings on
the server show the short date as dd/mm/yyyy format, CDate will read it
correctly.
 
Back
Top