VBA SystemTime - 30 days

Question

I am working on a code from a previous developer. This code has SystemTime set up. Is there a way to get today date and minus 30 days in this format?

Code Below:

Public Function GetIsoTimestampTest() As String
Dim st As SYSTEMTIME

'Get the local date and time
GetSystemTime st

'Format the result
GetIsoTimestampTest = _
    Format$(st.wYear, "0000") & "-" & _
    Format$(st.wMonth, "00") & "-" & _
    Format$(st.wDay, "00") & "T" & _
    Format$(st.wHour, "00") & ":" & _
    Format$(st.wMinute, "00") & ":" & _
    Format$(st.wSecond, "00") & "Z"
End Function

score 0 · Accepted Answer · answered May 16 '17 at 16:23

0

Build a native date & time, add -30 days, format as a string:

utcInIsoFormat = Format$(DateAdd("d", -30, _
    DateSerial(st.wYear, st.wMonth, st.wDay) _
    + TimeSerial(st.wHour, st.wMinute, st.wSecond)), "yyyy-mm-ddThh:nn:ssZ")

answered May 16 '17 at 16:23

Alex K.

171,639
30
264
288

score 0 · Answer 2 · answered May 16 '17 at 16:31

SYSTEMTIME appears to be a custom type defined elsewhere in your code. It's not a standard type available in Access VBA. So to use it effectively, you need to find the definition. Also GetSystemTime is also likely a custom function exclusive to your code. Here's a sample definition of a similar type, although it may not be exactly what's implemented in your system: http://custom-designed-databases.com/wordpress/2011/get-milliseconds-or-seconds-from-system-time-with-vba/

That said, System Time would refer to the Windows system time. You also have a native ability in VBA to get time using the Now() function. (https://msdn.microsoft.com/en-us/library/office/gg278671.aspx) This returns a variable with type Date, which is equivalent to a number where the integer represents days and the decimal represents time of day. An example to get 30 days prior to today would be:

Dim lastMonth as Date
Dim formattedDate as String    

lastMonth = Now() - 30
formattedDate = Format(lastMonth, "yyyy-mm-ddThh:nn:ssZ")

GetSystemTime() is a platform API, it returns the current time in UTC. — Alex K., May 16 '17 at 16:38
But it's not available in Access VBA natively. You have to create a function to access it the way the OP describes. — Scott, May 16 '17 at 16:39
Well you only have to declare it, point was it returns UTC time unlike Now() which is local time. — Alex K., May 16 '17 at 16:42

score 0 · Answer 3 · answered May 17 '17 at 08:43

DateSerial happily accepts a negative day count. Thus:

Public Function IsoDateMinus30() As Date

    Dim st As SYSTEMTIME
    Dim Result As Date

    ' Get the local date and time
    GetSystemTime st

    Result = DateSerial(st.wYear, st.wMonth, st.wDay - 30)

    ' To include time:
    '
    ' Result = _
    '     DateSerial(st.wYear, st.wMonth, st.wDay - 30) + _
    '     TimeSerial(st.wHour, st.wMinute, st.wSecond)    

    IsoDateMinus30 = Result

End Function

VBA SystemTime - 30 days

3 Answers3