Locale Formatted Dates

Use the CDate function to convert a string or number to a date. The CDate function performs a locale-specific conversion including the time component. The DateValue function removes the time portion by removing the fractional portion of the underlying Double. This causes unexpected results with some date values. See Listing 3 .

Listing 3: CDate returns a date and a time; DateValue removes the time.

 Dim dl As Date, d2 As Date dl = CDate("December 1, 1482 06:00:00") d2 = DateValue(dl) Print Now                   'for example, 08/16/2003 16:05:53 Print DateValue(Now)        'for example, 08/16/2003 Print dl & " = " & CDbl(dl) '12/01/1482 06:00:00 = -152325.75 Print d2 & " = " & CDbl(d2) '12/02/1482 = -152325 

 

Tip  

The OOo help says that DateValue returns a Long and that it's limited in range. It returns a Date, and the range is that of any Date value.

The last line of Listing 3 produces an unexpected result: The date returned by DateValue is 12/02/1482 but the date is actually 12/01/1482. This is because the date, when expressed as a Double, is negative (see Listing 4 ). The DateValue function returns unexpected values for dates before December 29, 1899. The negative numerical value represents an amount of time prior to the moment, corresponding to a zero value of the internal representation of the time.

Listing 4: DateValue unexpectedly truncates dates before December 29,1899.

 Dim dl As Date, d2 As Date, d3 As Date dl = CDate("12/28/1899") d2 = CDate("12/27/1899 06:00:00") d3 = CDate("12/27/1899") Print dl & " = " & CDbl(dl) '12/28/1899          = -2 Print d2 & " = " & CDbl(d2) '12/27/1899 06:00:00 = -2.75 Print d3 & " = " & CDbl(d3) '12/27/1899          = -3 

 

The representation of the date as a floating-point number is logical. The date 12/27/1899 corresponds to -3. Six hours corresponds to 0.25, so add 0.25 to -3 to obtain -2.75, that is, 12/27/1899 06:00:00. The problem is that DateValue truncates the decimal portion of the number to determine the date. The Int function, however, always rounds toward negative infinity, which produces the correct result. See Listing 5 .

Listing 5: Round toward negative infinity and convert it to a Date.

 Function SafeDateValue(v) Date   SafeDateValue = CDate(Int(CDate(v))) End Function 

 

SafeDateValue in Listing 5 has the same behavior for dates that are internally represented as a positive number and dates that are internally represented as a negative number. In both cases, the time value is simply removed. See Listing 6 .

Listing 6: Round toward negative infinity and convert it to a Date.

 Dim dl As Date, d2 As Date, d3 As Date dl = SafeDateValue("12/28/1899") d2 = SafeDateValue("12/27/1899 06:00:00") d3 = SafeDateValue("12/27/1899") Print dl & " = " & CDbl(dl)  '12/28/1899          = -2 Print d2 & " = " & CDbl(d2)  '12/27/1899 06:00:00 = -3 Print d3 & " = " & CDbl(d3)  '12/27/1899          = -3 

 

Bug  

DateValue fails with a run-time error for dates with a zero year component, such as DateValue (CDate("12/30/1899 06:00:00")). Dates before this return an incorrect value. I might also argue that dates and times that use the same data type is a bug, because it isn't possible to distinguish between a time and a date value for the day 12/30/1899.

The default language on the computers that I use is "English USA." To use a different locale, select Tools Options Language Settings Languages to open the Languages tab on the Options dialog. Then choose a locale. To test different locales, use the code in Listing 7 .

Listing 7: Print date- related information dependent on current locale.

 Dim d As Date d = CDate("1/2/3")   'You can use 1.2.3 or 1/2/3 regardless of locale Print d              'Prints locale specific Print Year(d) Print Month(d) Print Day(d) 

 

I ran the code in Listing 7 using four different locales: English USA, English UK, French France, and German Germany. The results are shown in Table 4 . The format used to print a date is locale-specific, as you can see in the Germany column. The CDate function accepts dates formatted using the period as a separator, even for the USA locale. Initializing d using CDate(" 1.2.3") rather than CDate(" 1/2/3") does not change the output in Table 4.

Table 4: Locale affects the date.

Code	USA	UK	France	Germany
Print d	"01/02/2003"	"01/02/2003"	"01/02/2003"	"01.02.2003"
Print Year(d)	2003	2003	2003	2003
Print Month(d)	1	2	2	2
Print Day(d)	2	1	1	1