Results 1 to 2 of 2

Thread: Parse international standard date & time notation

  1. #1

    Thread Starter
    VB6, XHTML & CSS hobbyist Merri's Avatar
    Join Date
    Oct 2002
    Location
    Finland
    Posts
    6,654

    Parse international standard date & time notation

    This is a generic function for parsing international standard date & time notation. It should accept almost any valid ISO 8601 notation. The only exceptions to this rule are timezone (Z is not supported) and fractions of a second (ie. milliseconds are not supported).

    More information about the international date & time notation

    The following inputs are valid:

    January 1st, 2010
    2010-01-01
    20100101

    Day #1, 2010 (January 1st, 2010)
    2010-001
    2010001

    December 31st, 2010
    2010-12-31
    20101231

    Day #365, 2010 (December 31st, 2010)
    2010-365
    2010365

    Week #1, 2010 (January 4th, 2010)
    2010-W01
    2010-W01-1 Monday
    2010-W01-7 Sunday


    All syntaxes also accept time (HH, HH:NN, and HH:NN:SS). Time must be separated from date by space or by letter T (as recommended by the standard). ":" characters are optional as "-" characters are optional for date separators.


    Code:
    'modInternationalDate.bas
    Option Explicit
    
    ' returns the date from a string formatted in ISO 8601 date & time notation
    Public Function IDate(Expression As String) As Date
        Dim DT() As String, lngLen As Long
        ' replace "T" -> " ", remove "-", ":"
        DT = Split(Replace(Replace(Replace(Expression, "-", vbNullString), ":", vbNullString), "T", " "))
        lngLen = Len(DT(0))
        ' date notation: by weeks or days?
        If InStr(DT(0), "W") = 5 Then
            ' remove W
            Mid$(DT(0), 5) = Mid$(DT(0), 6)
            lngLen = lngLen - 1
            DT(0) = Left$(DT(0), lngLen)
            ' validate
            If IsNumeric(DT(0)) Then
                IDate = DateSerial(Left$(DT(0), 4), 1, -1)
                Select Case lngLen
                    Case 7 ' 2010-W01-1 = by weeks + weekdays
                        IDate = DateAdd("WW", Mid$(DT(0), 5, 2), IDate - Weekday(IDate, vbWednesday) - 2 + Right$(DT(0), 1))
                    Case 6 ' 2010-W01 = by weeks
                        IDate = DateAdd("WW", Right$(DT(0), 2), IDate - Weekday(IDate, vbWednesday) - 1)
                    Case Else ' error, but we have a year...
                        IDate = IDate + 2
                End Select
            End If
        ElseIf IsNumeric(DT(0)) Then
            Select Case lngLen
                Case 8 ' 2010-01-01 = by months + days
                    IDate = DateSerial(Left$(DT(0), 4), Mid$(DT(0), 5, 2), Right$(DT(0), 2))
                Case 7 ' 2010-001 = by days
                    IDate = DateSerial(Left$(DT(0), 4), 1, Right$(DT(0), 3))
                Case 6 ' 2010-01 = by months
                    IDate = DateSerial(Left$(DT(0), 4), Right$(DT(0), 2), 1)
                Case 4 ' 2010 = by years
                    IDate = DateSerial(DT(0), 1, 1)
            End Select
        End If
        ' time notation
        If UBound(DT) > 0 Then
            Select Case Len(DT(1))
                Case 2
                    IDate = IDate + TimeSerial(DT(1), 0, 0)
                Case 4
                    IDate = IDate + TimeSerial(Left$(DT(1), 2), Right$(DT(1), 2), 0)
                Case 6
                    IDate = IDate + TimeSerial(Left$(DT(1), 2), Mid$(DT(1), 3, 2), Right$(DT(1), 2))
            End Select
        End If
    End Function
    
    ' returns ISO 8601 date & time using week notation
    Public Function IWeek(Expression As Date, Optional ByVal IncludeTime As Boolean) As String
        Dim D As Date, Y As Integer, W As Byte, E As Byte
        E = Weekday(Expression, vbMonday)
        Y = Year(Expression)
        D = DateSerial(Y, 1, -1)
        D = D - Weekday(D, vbWednesday) - 2 + E
        W = DateDiff("WW", D, Expression)
        If W = 53 Then If E < 4 Then W = 1: Y = Y + 1
        If Not IncludeTime Then
            IWeek = Right$("000" & Y, 4) & "-W" & Right$("0" & W, 2) & "-" & E
        Else
            IWeek = Right$("000" & Y, 4) & "-W" & Right$("0" & W, 2) & "-" & E & Format$(Expression, " HH\:NN\:SS")
        End If
    End Function
    Sample:
    Code:
    Option Explicit
    
    Private Sub Form_Load()
        Dim intYear As Integer
        For intYear = 2000 To 2010
            Debug.Print "Week #1 for year " & intYear, IDate(intYear & "-W01-1"), IIf(IDate(intYear & "-W53-1") = IDate((intYear + 1) & "-W01-1"), "52 weeks", "53 weeks")
        Next
        
        Debug.Print "Day 365 for year 2010", IDate("2010-365")
        
        Debug.Print "Dec 31th 2010, 23:59:59", IDate("2010-12-31 23:59:59")
    End Sub

    Please about post any trouble or incorrect results you may have with the function so they can be fixed. Also remember to tell which locale you have troubles with.


    Edit!
    Further reading for me: http://en.wikipedia.org/wiki/Seven-d...Week_numbering
    Last edited by Merri; May 22nd, 2010 at 02:09 PM. Reason: Bug fix: wrong variable in day calculation | IWeek change

  2. #2

    Thread Starter
    VB6, XHTML & CSS hobbyist Merri's Avatar
    Join Date
    Oct 2002
    Location
    Finland
    Posts
    6,654

    Re: Parse international standard date & time notation

    Just in case someone wonders why I don't use the DatePart function for week numbers, I noticed that it gives incorrect results.

    Code:
    Option Explicit
    
    Private Sub Form_Load()
        Dim intYear As Integer
        For intYear = 2000 To 2010
            Debug.Print DateSerial(intYear, 13, 0), IWeek(DateSerial(intYear, 13, 0)), DatePart("WW", DateSerial(intYear, 13, 0), vbMonday, vbFirstFourDays)
        Next
    End Sub
    Code:
    31.12.2000    2000-W52-7     52 
    31.12.2001    2002-W01-1     1 
    31.12.2002    2003-W01-2     1 
    31.12.2003    2004-W01-3     1 
    31.12.2004    2004-W53-5     53 
    31.12.2005    2005-W52-6     52 
    31.12.2006    2006-W52-7     52 
    31.12.2007    2008-W01-1     53 
    31.12.2008    2009-W01-3     1 
    31.12.2009    2009-W53-4     53 
    31.12.2010    2010-W52-5     52
    So that is why I had to write a "cumbersome" week numbering math. But as far as I can see it works correctly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width