ASP - Access97 - European dates?

[fintan]

I'm using an asp page to update records in an access 97 database. In the database the dates are displayed as dd/mm/yyyy, which is fine. When a new record is entered. It goes in the correct format "European". It keeps the Euro date format. Now if I go to update that record without changing a thing the dates switch from dd/mm to mm/dd. So 02/04 goes to 04/02. This only happens with numbers from 1-12, it reconizes 30/12 as dd/mm.

Does anyone know why?
Thanks

fintan.

[wardbekker]

fintan,

Check if all your locale settings are correctly set in ASP & the database connection.

[Woz]

If you always insert/update your dates as strings I think you should be ok.

IE, field.value = "'" & strDate & "'"

Onya
Woz

[fintan]

I've gone though my local machine, server, database, etc...
I'm thinking its down to the asp gone wrong some where.

<%
Session.LCID = 2057
EuDate = FormatDateTime(Date,vbShortDate)
Response.Write(EuDate)
%>

This returns the proper date from the server. Some things up with the insert/update behaviour. I wouldn't mind but I had this fixed, or so I thought. It only happens with numbers 1-12. I update say a date 04/02/2002, first try it goes in right but if I update the record the second time "without modifing any details" it becomes 02/04/2002.
Any ideas?

[wardbekker]

Hi fintan,

You could this function, to explicitly set the date variable:

Function localFormatDateTime(orgDate, format, locale)
Dim oldLocale
oldLocale = SetLocale(locale)
localFormatDateTime = Cstr(FormatDateTime(orgDate, format))
SetLocale(oldLocale)
End Function

[fintan]

I'll give a try and tell you how it goes in the morning.
Thanks

[fintan]

No effect that I can see, I got a script from another site. It should print out all the time/date settings. So I can see where I'm going wrong. We'll see how it goes

[wardbekker]

fintan,

Could you paste that script or drop me a stickynote with the url?

[aspdaddy]

This is a common bug you can get round it by always insert/update dates in this format:

dd-mmm-yyyy i.e 31-jan-2003

The year and day cant switch if you use 4 digits for year, the day and month cant switch if you use letters instead of numbers for month.

Get the mmm by using left(monthname(intMonth),3)

[fintan]

I'll try it and see what happens

[fintan]

I found this piece of code on a website, I thought I'd post it.

<%
'Function to format date string
Function format_date(date,dataformat)
months =Array("January","February","March","April","May","June","July","August","September","October","November","December")
mon = Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
'dd-MMM-yyyy = 0
'MM/dd/yyyy = 1
'dd MMMM,yyyy = 2
'MMMM dd,yyyy = 3
daypart = Day(date)
monthpart = Month(date)
yearpart = Year(date)
monthpart = monthpart -1
if(dataformat = 0) then
format_date = daypart & "-" & mon(monthpart) & "-" & yearpart
end if
if(dataformat = 1) then
format_date = monthpart & "-" & daypart & "-" & yearpart
end if
if(dataformat = 2) then
format_date = daypart & " " & months(monthpart) & " " & yearpart
end if
if(dataformat = 3) then
format_date = months(monthpart) & " " & daypart & "," & yearpart
end if

end Function

Function L_HTMLEncode(stringToEncode)
if IsNull(stringToEncode) OR IsEmpty(stringToEncode) then
L_HTMLEncode = stringToEncode
else
L_HTMLEncode = Server.HTMLEncode(stringToEncode)
end if
end Function
%>

[Woz]

Hmmm, ran into the same problem and discovered that I was wrong in my post above. After some research, and much pulling of hair, I discover that Access ALWAYS saves dates in MM/DD/YYY format, something I did not know.

So searching for a record with today's date can cause problems if you are on the this side of the pond.

The solution I came up with which seems to work is to force the date thus:-

"#" & Month(Date) & "/" & Day(Date) & "/" & Year(Date) & "#;"

so,

strSQL = "Select * from Table WHERE Datefield = #" & Month(Date) & "/" & Day(Date) & "/" & Year(Date) & "#;"

now works.

What I did then was to set a string at the start of the page like

strToday = Month(Date) & "/" & Day(Date) & "/" & Year(Date)

so whenever I needed to refer to today's date I inserted the string instead, thus:-

strSQL = "SELECT * from Table WHERE DateField = #" & strToday & "#;"

Try that fintan and see if it works for you.

Onya
Woz

[fintan]

Funny you should say that because I have been having trouble with some of the pages. Its having a problem with the month.

[Xoc]

The shortdate (and longdate) format uses the settings from the Regional Options from the Windows Control Panel, so you never really want to use them. I really recommend that anyone dealing with dates use ISO format which is YYYY-MM-DDTHH:MM:SS. There is no possibility of confusion with this date format, and it has the advantage that it sorts.