Benjamin Krajmalnik
|
Posted: 02/06/2006, 12:52 PM |
|
I just went through a very arduous process of internatinalizing our
application.
To simplify matters, global date format was set as "ShortDate" with no
overrides anywhere.
First step was to modify the common file. I use a configuration file where
we set the locale as well as a flag to know whether dates should be parsed
as mm/dd/yyyy or dd/mm/yyyy.
I am posting ASP code, but the same can be done in any of the languages.
First, CCFormatDate was modified as follows:
'CCFormatDate @0-3D9640B4
Function CCFormatDate(DateToFormat, FormatMask)
Dim ResultArray(), I, Result
If VarType(DateToFormat) = vbEmpty Then
Result = Empty
ElseIf VarType(DateToFormat) <> vbDate Then
Err.Raise 4000, "Common Functions. CCFormatDate function","Type
mismatch."
ElseIf IsEmpty(FormatMask) Then
Result = CStr(DateToFormat)
Else
' Begin code changes for international date formats
Dim MyMask
MyMask = FormatMask(0)
Select Case MyMask
Case "ShortDate"
Select Case MyDateFormat
Case 0 FormatMask = Array("mm","/","dd","/","yyyy")
Case else FormatMask = Array("dd","/","mm","/","yyyy")
End Select
Case "GeneralDate"
Select Case MyDateFormat
Case 0 FormatMask = Array("m","/","d","/","yy", " ","hh",":","nn","
","AM/PM")
Case else FormatMask = Array("d","/","m","/","yy", " ","hh",":","nn","
","AM/PM")
End Select
End Select
' End code changes for internatinal date formats
ReDim ResultArray(UBound(FormatMask))
For I = 0 To UBound(FormatMask)
Select Case FormatMask(I)
Case "d" ResultArray(I) = Day(DateToFormat)
Case "w" ResultArray(I) = Weekday(DateToFormat)
Case "m" ResultArray(I) = Month(DateToFormat)
Case "q" ResultArray(I) = Fix((Month(DateToFormat) + 2) / 3)
Case "y" ResultArray(I) = (DateDiff("d", "1/1" & "/" &
Year(DateToFormat), Month(DateToFormat) & "/" & Day(DateToFormat) & "/" &
Year(DateToFormat)) + 1)
Case "h" ResultArray(I) = CC12Hour(Hour(DateToFormat))
Case "H" ResultArray(I) = Hour(DateToFormat)
Case "n" ResultArray(I) = Minute(DateToFormat)
Case "s" ResultArray(I) = Second(DateToFormat)
Case "dd" ResultArray(I) = CCAddZero(Day(DateToFormat), 2)
Case "ww" ResultArray(I) = (DateDiff("ww", "1/1" & "/" &
Year(DateToFormat), Month(DateToFormat) & "/" & Day(DateToFormat) & "/" &
Year(DateToFormat)) + 1)
Case "mm" ResultArray(I) = CCAddZero(Month(DateToFormat), 2)
Case "yy" ResultArray(I) = Right(Year(DateToFormat), 2)
Case "hh" ResultArray(I) = CCAddZero(CC12Hour(Hour(DateToFormat)),
2)
Case "HH" ResultArray(I) = CCAddZero(Hour(DateToFormat), 2)
Case "nn" ResultArray(I) = CCAddZero(Minute(DateToFormat), 2)
Case "ss" ResultArray(I) = CCAddZero(Second(DateToFormat), 2)
Case "S" ResultArray(I) = "000"
Case "ddd" ResultArray(I) =
CCSDateConstants.ShortWeekdays(Weekday(DateToFormat) - 1)
Case "mmm" ResultArray(I) =
CCSDateConstants.ShortMonths(Month(DateToFormat) - 1)
Case "A/P" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "A", "P")
Case "a/p" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "a", "p")
Case "dddd" ResultArray(I) =
CCSDateConstants.Weekdays(Weekday(DateToFormat) - 1)
Case "mmmm" ResultArray(I) =
CCSDateConstants.Months(Month(DateToFormat) - 1)
Case "yyyy" ResultArray(I) = Year(DateToFormat)
Case "AM/PM" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "AM",
"PM")
Case "am/pm" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "am",
"pm")
Case "LongDate" ResultArray(I) = FormatDateTime(DateToFormat,
vbLongDate)
Case "LongTime" ResultArray(I) = FormatDateTime(DateToFormat,
vbLongTime)
Case "ShortDate" ResultArray(I) = FormatDateTime(DateToFormat,
vbShortDate)
Case "ShortTime" ResultArray(I) = FormatDateTime(DateToFormat,
vbShortTime)
Case "GeneralDate" ResultArray(I) = FormatDateTime(DateToFormat,
vbGeneralDate)
Case Else
If Left(FormatMask(I), 1) = "\" Then _
ResultArray(I) = Mid(FormatMask(I), 1) _
Else
ResultArray(I) = FormatMask(I)
End Select
Next
Result = Join(ResultArray, "")
End If
CCFormatDate = Result
End Function
'End CCFormatDate
Note that I am referring to a variable called "MyDateFormat", which is
defined in the includable config file.
Next step, is to internationalize DatePicker.js do it will return the
correct format. This was done by adding a variable at the beginning of the
script and having conditionals when parsing out the date.
Now, the most important part:
On the server, set the date format as "yyyymmdd HH:nn:ss".
Note there are no dashes in between the year, month, and date. This is the
ISO representation of the date and the only one which will not be
"localized" by the SQL Server where localizatins are in place. In our case,
we are using SQL Server 2000.
Internal testing on our server which is set up for US locale had worked fine
with the "yyyy-mm-dd HH:nn:ss" date format, regardless of the data
presentation. However, when a client in Australia deployed our product we
came across errors in the conversion of dates. By changing the format to
ISO format everything was resolved.
I have tested ISO syntax on SQL Server, MySQL and PostgreSQL, and it works
flawlessly on all. I assume the same will be the case with other SQL
servers.
Now, a small feature request from Yes. Have the ISO format available as a
dropdown of the different database date formats.
|
|
|
|