Apex Code Development You need to sign in to do that
Don't have an account?
deepbluesea sForce Connector: localized typeToFormat()
Hi Ron,
in your util module you asked for suggestions as how to pass a localized date format. If you don't want to use WINAPI32 calls, then using DateSerial() and modifying the result is probably the easiest way to do it. This way, you don't have to worry about the order of years/months/days, 2- or 4-digit years, leading zeroes in days/months or separators, which can be any of the following /.'- (and maybe more). All you have to do is take the result and replace all numeric occurrences with their respective format characters, and leave everything else as is. This can be done inside a For-Loop with Mid$() function calls, with subsequent MID$() method calls or with the Replace() function. I prefer the latter, because it's easiest to understand and very straightforward, but other approaches may be faster or more appealing to the eye. Here's my suggestion:
Function typeToFormat(sfType As String)
typeToFormat = "General" ' default
Select Case sfType
Case "date", "datetime"
Dim dateTemp As String
dateTemp = DateSerial(2134, 6, 5)
dateTemp = Replace(dateTemp, "2134", "yyyy")
dateTemp = Replace(dateTemp, "34", "yy") 'year may be 2 characters only
dateTemp = Replace(dateTemp, "06", "mm")
dateTemp = Replace(dateTemp, "6", "m") 'month format may not have leading zero
dateTemp = Replace(dateTemp, "05", "dd")
dateTemp = Replace(dateTemp, "5", "d") 'day format may not have leading zero
If sfType = "datetime" Then
dateTemp = dateTemp & " h:mm"
End If
typeToFormat = dateTemp
'Case "date"
'typeToFormat = "m/d/yyyy" ' wow, cant figure out how to pass a localized format, bummer
'Case "datetime"
'typeToFormat = "m/d/yyyy h:mm" ' 5.15
Case "string", "picklist", "phone" ' , "textarea"
typeToFormat = "@"
Case "currency"
typeToFormat = "$#,##0_);($#,##0)" ' format as currency, no cents (added in 5.15)
End Select
End Function
I like it,
so, if i understand, this takes advantage of the fact that DateSerial() generates a localized format string, which I then reverse engineer and apply to the date I'm actually trying to format.
i'll give this a spin soon, thanks !
Hi again,
in my example, I forgot to mention that DateSerial() uses the Windows' short date format, as can either be set in the Control Panel / Regional Settings manually or with API calls (hence my reference to the WIN32API). I don't know if any languages offer the "full" month's name (e.g. "January") in the short date format, but I sincerely doubt it (neither german nor english allow that). This is why my code doesn't try to handle full months at all.
I have written some VB code utilizing the WIN32API to achieve the same results, but I couldn't find it (yet). If someone has the syntax, then please either post it here or send me a line to erik.mittmeyer@softwareag.com. If I find it myself, then I will post it here.
Thanx,
Erik
Hi Ron,
I'm glad you liked it, but I don't think you'll need it after all. I have found that all dates are imported to Excel in perfectly correct german format ("dd.mm.yyyy hh:nn:ss") regardless of what date format (even nonsensical) I provide. I think that Excel ignores the format altogether and automatically returns the localized dates, rather than forcing any format on the cells. Am I missing something?
Cheers, Erik
PS: I have reproduced the API code for retrieving localization values as mentioned in my last post, including short and long dates, weekday and month representations in long ("Monday"/"January") and short formats ("Mo"/"Jan") and much more (I can provide a list of all consts, I just didn't want my post to be so large). Here's the relevant extract and your modified typeToFormat() function (I didn't bother to change the currency format, though, but this also can be retrieved):
==========================================================
Option Explicit
Private Declare Function GetSystemDefaultLCID _
Lib "kernel32" () As Long
Private Declare Function GetLocaleInfo _
Lib "kernel32" _
Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, _
ByVal cchData As Long) As Long
'** extract from Win32API.bas:
Const LOCALE_SSHORTDATE = &H1F
Const LOCALE_STIMEFORMAT = &H1003
Private Function GetLocaleValue(ByVal lngLCparam As Long) As String
Dim lngLCID As Long
Dim lngDummy As Long, lngLength As Long
Dim strBuffer As String
lngLCID = GetSystemDefaultLCID()
lngLength = GetLocaleInfo(lngLCID, lngLCparam, strBuffer, 0) - 1
strBuffer = Space(lngLength + 1)
lngDummy = GetLocaleInfo(lngLCID, lngLCparam, strBuffer, lngLength)
GetLocaleValue = Left$(strBuffer, lngLength)
End Function
==================================================================
Public Function typeToFormat(sfType As String)
typeToFormat = "General" ' default
Select Case sfType
Case "date", "datetime"
Dim s As String
s = GetLocaleValue(LOCALE_SSHORTDATE)
If sfType = "datetime" Then
s = s & " " & GetLocaleValue(LOCALE_STIMEFORMAT)
End If
typeToFormat = s
Case "string", "picklist", "phone" ' , "textarea"
typeToFormat = "@"
Case "currency"
typeToFormat = "$#,##0_);($#,##0)" ' format as currency, no cents (added in 5.15)
End Select
End Function