Discover New Possibilities!

Format Date String

Recording date strings is quite common in logging errors or events. The specific layout of the date string varies from application to application as well as geographic location. Having an easy method to change date formats was the motivation for this code. The "token" approach may seem similar to other scripting languages. This is intentional as the similarity hopefully simplifies things for developers who program in more than one language.

The mini-toolkit comprises of the following functions:

Function tokenDate( ByVal sInput, ByVal sDate )
  sInput = Replace( sInput, "%Y", _
                    & Year( sDate ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%y", _
                    & Right( Year( sDate ), 2 ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%M", _
                    & Month( sDate ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%m", _
                    & ZeroPad( Month( sDate ), 2 ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%D", _
                    & Day( sDate ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%d", _
                    & ZeroPad( Day( sDate ), 2 ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%F", _
                    & MonthName( Month( sDate )), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%f", _
                    & MonthName( Month( sDate ), True ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%W", _
                    & WeekdayName( Weekday( sDate )), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%w", _
                    & WeekdayName( Weekday( sDate ), True ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%U", _
                    & epochSecs( sDate ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%u", _
                    & unixDay( sDate ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%h", _
                    & Hour( sDate ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%i", _
                    & ZeroPad( Minute( sDate ), 2 ), 1, -1, vbBinaryCompare )
  sInput = Replace( sInput, "%s", _
                    & ZeroPad( Second( sDate ), 2 ), 1, -1, vbBinaryCompare )
  tokenDate = sInput
End Function

Function ZeroPad( ByVal sNumber, ByVal nPadFactor )
    	ZeroPad = Right( String( nPadFactor, "0" ) & Trim( sNumber ), nPadFactor )
End Function

Function epochSecs( ByVal julianDate )
  epochSecs = DateDiff( "s", "01/01/1970 00:00:00", julianDate )
End Function

Function unixDay( ByVal julianDate )
  unixDay = DateDiff( "d", "01/01/1970", julianDate )
End Function