asked on

LCID: your experiences and explanations

Lately I have been looking to the "Locale" settings of ASP and databases. I got a bit confused, and I would like some expert input in the issue, and also create a nice PAQ.

I have many points to give, and will give points to anyone who has a usefull contribution to this discussion.

There are 3 "Locale" setting in an ASP-application (that is I found 3 untill now).

1. Session.LCID
2. GetLocale (and SetLocale)
3. connection.Properties("Locale Identifier")

The first two can be changed in ASP script, the last can not be changed (that one is read-only and it generates an error when trying to change it)

The two script LCID are not per definition identical. I have them natively different on my machine. And I can change them independently.
But there are some exceptions: after Session.LCID = 1033 (US setting), then the GetLocale also changes to 1033.

Also I noticed that some locale setting are "not available". Trying to set my LCID to German (1049) gives me an error - apparently because the german setting are not installed.

For seeing what it does, I displaced Now() after each change of settings.

Most of the time, the last change sets the datetimeformat. But not always.
After:
<%
SetLocale(1043) ' set to Ducth
Session.LCID = 1033 ' set to US
%>
The date is still displayed in Dutch format.

Ok, so here is my test code:

===================================

<%
Option Explicit

Dim sDSN, oConn, iLCID_Locale, iLCID_Db, iLCID_Session
Dim iOriginalLocaleLCID, iOriginalSessionLCID, iOriginalDBLCID
sDSN = "provider=Microsoft.jet.oledb.4.0; data source = " & Server.MapPath("/sybe") & "\_db\test_all.mdb;"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open sDSN

' getting starting values

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

iOriginalLocaleLCID = iLCID_Locale
iOriginalSessionLCID = iLCID_Session
iOriginalDBLCID = iLCID_Db

Response.write "<hr>original settings"
Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

' changing Session.LCID
Response.write "<hr>after setting Session.LCID = 1043"
Session.LCID = 1043

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

Response.write "<hr>after setting Session.LCID = 1033"
Session.LCID = 1033

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

Response.write "<hr>after setting Session.LCID = 2048"
Session.LCID = 2048

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

' changing with SetLocale
Response.write "<hr>after setting SetLocale(1043)"
SetLocale(1043)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

Response.write "<hr>after setting SetLocale(2048)"

SetLocale(2048)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

Response.write "<hr>after setting SetLocale(1033)"
SetLocale(1033)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

Response.write "<hr>after setting Session.LCID = 1033 and SetLocale(1043) "
Session.LCID = 1033
SetLocale(1043)

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

Response.write "<hr>after setting SetLocale(1043) and Session.LCID = 1033 "
SetLocale(1033)
Session.LCID = 1043

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

Response.write "<hr>after setting SetLocale(1033) and Session.LCID = 1043 "
SetLocale(1033)
Session.LCID = 1043

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

SetToOriginal

' setting back to original
SetToOriginal

Response.write "<hr>after resetting to original"

iLCID_Locale = GetLocale
iLCID_Session = Session.LCID
iLCID_Db = oConn.Properties("Locale Identifier")

Response.write "Locale LCID = " & iLCID_Locale & " "
Response.write "Session LCID = " & iLCID_Session & " "
Response.write "Database LCID = " & iLCID_Db & " "
Response.write Now() & " "

oConn.Close
Set oConn = Nothing

Sub SetToOriginal()
Session.LCID = iOriginalSessionLCID
SetLocale(iOriginalLocaleLCID)
End Sub

%>

===================================

and here is my result
===================================

----------------------------------------------------------original settings
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after setting Session.LCID = 1043
Locale LCID = 1043
Session LCID = 1043
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after setting Session.LCID = 1033
Locale LCID = 1033
Session LCID = 1033
Database LCID = 1033
12/13/2002 11:08:02 AM

----------------------------------------------------------after setting Session.LCID = 2048
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after setting SetLocale(1043)
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after setting SetLocale(2048)
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after setting SetLocale(1033)
Locale LCID = 1033
Session LCID = 2048
Database LCID = 1033
12/13/2002 11:08:02 AM

----------------------------------------------------------after setting Session.LCID = 1033 and SetLocale(1043)
Locale LCID = 1043
Session LCID = 1033
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after setting SetLocale(1043) and Session.LCID = 1033
Locale LCID = 1043
Session LCID = 1043
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after setting SetLocale(1033) and Session.LCID = 1043
Locale LCID = 1043
Session LCID = 1043
Database LCID = 1033
13-12-2002 11:08:02

----------------------------------------------------------after resetting to original
Locale LCID = 1043
Session LCID = 2048
Database LCID = 1033
13-12-2002 11:08:02

sybe

ASKER

I am interested in things like:

Which (registry-) settings control the different LCID's.

Which LCID determines what.

Why are Get/SetLocale and Session.LCID sometimes independent and sometimes not.

markhoy

Is this a post to discuss the problems with locale or to identify a way to format dates? :-)

I try to avoid LCID and use the date format in the database of dd-mmm-yy (or dd-mmm-yyyy) e.g 18-DEC-2002

Absolutely no ambiguity. You can then format this however you want.. I use a custom formatDate function:

Function DateConvert(MyDate)

Dim strDay
Dim intDate
Dim strMonth
Dim intyear
Dim WDay
Dim MMonth
Dim int4digyear

WDay = WeekDay(MyDate)
MMonth = Month(MyDate)
strDay = WeekDayName(Wday)
intDate = Day(MyDate)
strMonth = MonthName(MMonth)
intYear = Year(MyDate)
int4digyear = Right(intYear,4)

DateConvert = " " & strDay & ", " & _
intDate & " " & strMonth & " " & int4digyear
End Function

Quiet day at work?!

mark

sybe

ASKER

Yes, dateformat has to do with it.

>> I try to avoid LCID and use the date format in the database of dd-mmm-yy (or dd-mmm-yyyy) e.g 18-DEC-2002

This does not work always. For example a Dutch-LCID database won't accept 18-MAY-2002. This goes only wrong with May and October, where the Ducth and US month abbreviations differ. But a dateformat solution should work always, not most of the time :)

sybe

ASKER

I use a similar function, but using date-format
"Dec 13, 2002", and handling time and invalid datestrings.

But as said, it fails when ASP-LCID and Database LCID differ.

Function DBDate(ByVal var)
Dim aMonthNames, dtTemp, iTotalSeconds, dtTime

aMonthNames = Array(MonthName(1,True),MonthName(2,True),MonthName(3,True),MonthName(4,True),MonthName(5,True),MonthName(6,True),MonthName(7,True),MonthName(8,True),MonthName(9,True),MonthName(10,True),MonthName(11,True),MonthName(12,True))
On Error Resume Next
dtTemp = CDate(Cstr("" & var))
If Err Then Exit Function
DBDate = aMonthNames(Month(dtTemp)-1) & " " & Day(dtTemp) & ", " & Year(dtTemp)
If Err Then Exit Function
iTotalSeconds = DateDiff("s",CDate(DBDate),dtTemp)
If iTotalSeconds > 0 Then
dtTime = CDate(CDate(DBDate)-dtTemp)
DBDate = DBDate & " " & dtTime
End If
End Function

markhoy

I personally would stick to the normal database month convention and use a function to replace the month abbreviation when used on screen. That way if you port your database to another country, server, wherever the data will be OK. Like a lookup table I guess. Set up a application variable in the global.asa depending upon country implementation and then use that on each page to set the display as you want.. Does that make sense? Haven't been to the pub yet (7 hrs to go :-)) and I've already got a headache...

sybe

ASKER

Well, the problem is not with displaying date variables. As soon as you have a variable of type "Date", then it is no problem to display it in any desired format.

The problem is the other way around: how to get dates, that are formatted as a string, into the database as a date, in a way that the code will work on any server, no matter what the regional setting are.

So far I have only found ways in which is somewhere hardcoded what the date-format of the database is.
And I don't like hardcoding stuff like date-format.

Asking the database what its internal monthnames are, seems possible, but fails with ADBODB.

SELECT Monthname(1)+","+Monthname(2)+","+Monthname(3)+","+Monthname(4)+","+Monthname(5)+","+Monthname(6)+","+Monthname(7)+","+Monthname(8)+","+Monthname(9)+","+Monthname(10)+","+Monthname(11)+","+Monthname(12) AS monthnames

works directly in ACCESS, but when run from ASP, JET Driver does not recognize the Monthname-functions and gives an error.

jitganguly

My 2 cents

I have never used it. but suggested lots of people and all (or at least 80%) of them came back saying "Not working".
So, I had to suggest them to change regional setting to the specific country

ploftin

If you are needing a way to standardize the format in which the date is submitted to the server, you can use the following function that will convert a date into ISO format, which is identical (at least for Access) no matter what country you live in.

Function IsoDate(dteDate)
If IsDate(dteDate) = True Then
DIM dteDay, dteMonth, dteYear
dteDay = Day(dteDate)
dteMonth = Month(dteDate)
dteYear = Year(dteDate)
IsoDate = dteYear & _
"-" & Right(Cstr(dteMonth + 100),2) & _
"-" & Right(Cstr(dteDay + 100),2)
Else
IsoDate = Null
End If
End Function

hongjun

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
[PAQ with REFUND]

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

hongjun
EE Cleanup Volunteer

ASKER CERTIFIED SOLUTION

YensidMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial