A better CDate for VB6

Question

We have a a VB6 app (in a COM component) which uses CDate() to take a string and cast it to a Date, for storing in a database.

Depending on if we want the application to talk in dd/MM/yy or MM/dd/yy for example, we have to change the regional settings of the identity user for the COM application. (Right now the only option we have is a nasty hack.)

We have a date format string which is used for formatting all output dates, and it is assumed that the date

If this were .NET we would use DateTime.ParseExact and be away laughing. Calling out to a COM object written in .NET for this sole purpose is an option. Is there a different or better option, involving some black magic around the Format command, or a long reusable function that tokenizes the date depending on the format string, etc?

Solution 1

This should be close, though it hardcodes the delimiter as "/" and windows YY years at 50:

Private Function ParseDate(ByVal DateString As String, _
                           ByVal DatePattern As String) As Date
    'DateString:  i/j/k formatting.
    'DatePattern: i/j/k formatting, each to be:
    '               M or MM for month position.
    '               D or DD for day position.
    '               YY or YYYY for year position, if YY
    '                 then century windowed at 50.
    Dim strStringParts() As String
    Dim strPatternParts() As String
    Dim intPart As Integer, intScore As Integer
    Dim intMonth As Integer, intDay As Integer, intYear As Integer
    Const DELIM As String = "/"
    Const YYWINDOW As Integer = 50

    strStringParts = Split(DateString, DELIM)
    strPatternParts = Split(UCase$(DatePattern), DELIM)
    For intPart = 0 To UBound(strStringParts)
        If intPart > UBound(strPatternParts) Then
            Err.Raise 5, "ParseDate"
        End If
        Select Case strPatternParts(intPart)
            Case "M", "MM"
                intMonth = CInt(strStringParts(intPart))
                intScore = intScore Or &H1
            Case "D", "DD"
                intDay = CInt(strStringParts(intPart))
                intScore = intScore Or &H2
            Case "YY"
                intYear = CInt(strStringParts(intPart))
                If 0 > intYear Or intYear > 99 Then
                    Err.Raise 5, "ParseDate"
                End If
                intYear = intYear + IIf(intYear < YYWINDOW, 2000, 1900)
                intScore = intScore Or &H4
            Case "YYYY"
                intYear = CInt(strStringParts(intPart))
                If 100 > intYear Or intYear > 9999 Then
                    Err.Raise 5, "ParseDate"
                End If
                intScore = intScore Or &H4
            Case Else
                Err.Raise 5, "ParseDate"
        End Select
    Next
    If intScore = &H7 Then
        ParseDate = DateSerial(intYear, intMonth, intDay)
    Else
        Err.Raise 5, "ParseDate"
    End If
End Function

Validation may not be perfect, but it ought to be close. It throws "Invalid procedure call or argument (Error 5)" on bad inputs.

Solution 2

Look, there's no easy way to say this - you're screwed. If you accept freeform input from the web, you have to live with the reality that people around the world format dates differently. That's why so many websites use popup calendars, and such, to get user input. No ambiguity there. No matter what you think, .NET's library routines can't fathom your user's intentions any better than any other library.

Fwiw, the code Mike posted is absolutely VB6. I'm not sure what about it looks like VB.NET? Once you get a date/time into a Date variable, you can display it however you want with Format(). That's the easy part.

I'd strongly suggest you either A) find a way to gather your inputs unambiguously, or B) tell your users what format you expect and live with what they input. All that said, is it possible I misunderstood the question, and you really do know what format the user is providing the data in? (Because if so, I really am having trouble understanding what's the problem interpreting it in ClassicVB - sorry.)

Solution 3

DateAdd accepts a wide variety of inputs and outputs in the correct format.

ThisLine =  "Tuesday, September 04, 2012 2:02 PM"

i = InStr(ThisLine, ",")  ' get rid of the leading day

If i > 0 Then
     TempResult = Trim(Right$(ThisLine, Len(ThisLine) - i))
end if

TempResult = DateAdd("s", 0, TempResult)

Solution 4

Another way to go:

Public Enum abDateType
    abMDY
    abDMY
End Enum

Public Function MakeDate(ByVal dateString As String, ByVal dateType As abDateType, Optional delimiter As String = "/") As Date
    Dim strVals() As String
    Dim dtRtnVal As Date
    strVals = Split(dateString, delimiter)
    Select Case dateType
    Case abMDY
        dtRtnVal = DateSerial(strVals(2), strVals(0), strVals(1))
    Case abDMY
        dtRtnVal = DateSerial(strVals(2), strVals(1), strVals(0))
    Case Else
        Err.Raise vbObjectError, , "Unexpected date format."
    End Select
    MakeDate = dtRtnVal
End Function

Solution 5

I don't know of an easy solution. You could Split the input string into sub-strings by the delimiter, and then use DateSerial to recombine the year, month and hour numbers into a native VB6 Date variable. Something like this below. If you need to support a lot of locales, this could get complicated (see Bob's answer). Mind you, so would using DateTime.ParseExact.

sInput = "1/3/71"
Dim splt() As String
splt = Split(sInput, "/")
dte = DateSerial(splt(2) + 1900, splt(1), splt(0))  ' dd/mm/yy'

View more solutions