I've used the Microsoft Date and Time Picker (DTPicker) ActiveX control for years. Yesterday, I ran into an interesting issue. First, some background.
The control has four different data entry modes, set via the Format property: short date, long date, time, and custom. The nice thing about the first three is that they automatically use the date/time settings you set in the Regional Settings applet in the Control Panel, so you don't have to worry about localization issues. Custom is used for custom formats.
Time format is fine if you just want the time, but if you want both date and time displayed, you have to set Format to 3 for custom, then set the CustomFormat property to the desired format. For example, "MM/dd/yyy HH:mm:ss" uses two digits for most values and four digits for year (yes, four even though the format string uses three). However, here's the issue: how do you know what format to use? The user could be using MM/DD/YYYY, DD/MM/YYYY, or any of a variety of formats.
Fortunately, VFP has several functions that return the formats for dates. SET("DATE") returns values like MDY or American for MM/DD/YYYY format, BRITISH or DMY for DD/MM/YYYY format, and so on. SET("MARK") returns the character separating parts of the date (such as "/" or "-").
I have a subclass of the DTPicker control called SFDatePicker (actually, it's a container that contains a DTPicker) that provides additional functionality, including empty date support, data binding, and format control. Format control is handled via a custom lDateTime property; the default of .F. means only the date appears while .T. means date and time. Drop one on a form, set cControlSource to the control source if desired, set lDateTime to .T. if you want date and time, and you're done.
Except someone from Australia, which uses DD/MM/YYYY as its date format, reported that if they specify that Stonefield Query should display a datetime field as both the date and time rather than the default date only, when they filter on that field, the filter dialog displays the date as MM/DD/YYYY.
The following code in the SetCustomFormat method of SFDatePicker, which is called from Init and the Assign method of lDateTime (in case you change this property programmatically), sets CustomFormat as necessary:
with This
lcFormat = set('DATE')
if lcFormat <> 'SHORT' or .lDateTime
.oleDTPicker.Object.Format = 3
lcMark = set('MARK')
do case
case inlist(lcFormat, 'AMERICAN', 'MDY', 'USA', 'SHORT')
lcCustomFormat = 'MM' + lcMark + 'dd' + lcMark + 'yyy'
case inlist(lcFormat, 'BRITISH', 'DMY', 'FRENCH', ;
'GERMAN', 'ITALIAN')
lcCustomFormat = 'dd' + lcMark + 'MM' + lcMark + 'yyy'
case inlist(lcFormat, 'JAPAN', 'YMD', 'TAIWAN', 'ANSI')
lcCustomFormat = 'yyy' + lcMark + 'MM' + lcMark + 'dd'
endcase
if .lDateTime
lcCustomFormat = lcCustomFormat + ' HH:mm:ss'
endif .lDateTime
.oleDTPicker.CustomFormat = lcCustomFormat
endif lcFormat <> 'SHORT' ...
endwith
It looks like this code handles different date formats properly, including the date separator, so what's the problem? It's a subtle one: if you use SET SYSFORMATS ON, which you should so the user's regional settings are respected, SET("DATE") returns "SHORT". This code assumes that short dates are treated as MDY.
Now the problem is how to determine what the user's actual date format is. I figured a Windows API function would take care of it, and did find some possibilities on MSDN, but it looks like these functions can't be called directly from VFP because they require callback functions. So, I took a brute force approach:
if lcFormat = 'SHORT'
ldDate = date(2008, 1, 3)
lcDate = dtoc(ldDate)
lnPos1 = at('8', lcDate)
lnPos2 = at('1', lcDate)
lnPos3 = at('3', lcDate)
do case
case lnPos1 < lnPos2 and lnPos2 < lnPos3
lcFormat = 'YMD'
case lnPos1 > lnPos2 and lnPos2 > lnPos3
lcFormat = 'DMY'
case lnPos1 > lnPos3 and lnPos3 > lnPos2
lcFormat = 'MDY'
endcase
endif lcFormat = 'SHORT'
This code uses 01/03/2008 (in MDY format) as a date, converts it to a string (which respects the user's regional settings), then figures out what order the month, day, and year parts are in and sets lcFormat accordingly.
Ugly, yes, but it works, so I'll stick with it until a more elegant solution is available.
Update: I figured there was a better way to do this. While I was looking at the code for Carlos Alloatti's cool ctl32_datepicker control, I came across his use of SET('DATE', 1). Looking that up in the VFP help file, I discovered this was exactly the function I needed. I can't believe I either never knew about that or (more likely) forgot about it. So, now the code is a much simpler and cleaner:
if lcFormat = 'SHORT'
lnDate = set('DATE', 1)
do case
case lnDate = 0
lcFormat = 'MDY'
case lnDate = 1
lcFormat = 'DMY'
otherwise
lcFormat = 'YMD'
endcase
endif lcFormat = 'SHORT'
If you're curious about how the other features work, here are the details.
Control source support: as I mentioned above, cControlSource is a custom property containing the name of the control source if desired. There's also a Value property so the control can look like other data-bound controls. Refresh updates Value from the control source:
if not empty(This.cControlSource)
This.Value = evaluate(This.cControlSource)
endif not empty(This.cControlSource)
The Change event of the DTPicker, which fires when the user changes the date and/or time, raises a custom DateChanged event of the container. DateChanged updates the control source, which could either be a field in a cursor or something else, such as a property of an object:
with This
lcAlias = juststem(.cControlSource)
lcField = justext(.cControlSource)
do case
case empty(.cControlSource)
case used(lcAlias)
replace &lcField with .Value in (lcAlias)
otherwise
store .Value to (.cControlSource)
endcase
endwith
Value_Access gets the value from the DTPicker, changing it to a date if a datetime isn't needed:
luValue = This.oleDTPicker.Object.Value
if not This.lDateTime
luValue = ttod(luValue)
endif not This.lDateTime
return luValue
Blank date support: if you've worked with the DTPicker control, you know it doesn't like blank dates. So, the code in Value_Assign uses the current datetime in that case. Also, since DTPicker expects to have its Value property set to a datetime value, we have to handle being passed a date:
lparameters tuValue
local luValue
with This
do case
case empty(tuValue)
luValue = datetime()
case vartype(tuValue) = 'D'
luValue = dtot(tuValue)
case vartype(tuValue) = 'T'
luValue = tuValue
otherwise
luValue = .NULL.
endcase
if not isnull(luValue)
try
.oleDTPicker.Object.Value = luValue
if not .CalledFromThisClass()
raiseevent(This, 'DateChanged')
endif not .CalledFromThisClass()
catch
endtry
endif not isnull(luValue)
endwith
Posts
