Warning about Now() function / region time date format change

[tmighty2]

Hello!

I am not sure if this is new only to me, but to me it was:

I noticed that Windows 11 does not show the weekday when I hover over the date in the taskbar.
In fact it does not do anything when I click the date / time in the taskbar on Windows 11.

As the weekend is pretty important to me, I googled and found the suggestion to change the "short date" format.

I did that in the "administrative language options": I added "TTTT" to the short date. That resulted in the taskbar showing the weekday again in the taskbar.

I am using the following code to update the date time modified in a RC6 sqlite command statement like that:

    Dim Cmd As cCommand
    Set Cmd = CnUser.CreateCommand("UPDATE pages SET pagedatetimemodified=?,pagethumbdirty=? WHERE pageguid=? AND pagebookguid=?")

    Cmd.SetDate 1, Now
    Cmd.SetBoolean 2, True
    Cmd.SetText 3, g_currentPage.Guid
    Cmd.SetText 4, tBook.Guid

    Cmd.Execute

My change of the short date would cause "Now" to be for example "Monday, 19.02.2024 02:39:93".

The cCommand would accept that for date and VB6's "date" type would, too.

I guess it's my own fault, but while being a beginner at SQLite and not having a clear concept about the data types (and I guess also switching between ADO and other formats), I found myself in a situation where "" or NULL was stored in a SQLite column.

That is why I invented a NoNull function:

Public Function NoNull(ByVal uAny As Variant, Optional ByVal uFillString As String, Optional ByVal uTreatDecimalNullAsNothing As Boolean = False) As String
On Error GoTo ErrHandler

    If Not IsNull(uAny) Then
        NoNull = uAny
    Else
        NoNull = ""
    End If

    If VBA.Len(NoNull) = 0 Then
        If VBA.Len(uFillString) > 0 Then
            NoNull = uFillString
        End If
    End If
    
    If uTreatDecimalNullAsNothing Then
        If NoNull = "0" Then
            NoNull = uFillString
        End If
    End If

Exit Function
ErrHandler:
Debug.Print Err.Description
Call CriticalLog("#NoNull: " & Err.Description & ", err.number: " & Err.Number & ", Params: '" & "" & "'")
On Error GoTo -1
Debug.Assert False
End Function

And this function would not accept the new format.
This would throw an error:

Dim d As Date
d = NoNull(r!PageDateTimeModified, "00:00:00")

The function will do this:

    If Not IsNull(uAny) Then
        NoNull = uAny'Function will say that "Mittwoch, 19.02.2025 02:12:43" is not null and will return this as the result of the function

"d" however will not accept this as a date and throw "Type Mismatch".

Perhaps more people will have customers making this change on their Windows 11, so perhaps my report is helpful to somebody.

It's quite interesting as it's not simple to get rid of the weekday name.

This function is not able to remove it:

Public Function FormatDateEx(ByVal u As Date) As Date
    Debug.Print u
    Dim d As Date
    d = Format(u, "yyyy-mm-dd HH:nn:ss")
    FormatDateEx = d
End Function

[tmighty2]

Edit: I think it's worse than I thought as I think that VB6 now even expects a date to be formatted with weekday included.

This would also throw a type mismatch:

  
Public Function FormatDateEx(ByVal u As Date) As Date
  Debug.Print u
    Dim d As Date
    
    Dim sDate$
    sDate = Format(year(u), "0000") & "-" & Format(month(u), "00") & "-" & Format(day(u), "00")

    Dim sTime$
    sTime = Format(Hour(u), "00") & "-" & Format(Minute(u), "00") & "-" & Format(Second(u), "00") ' '& millisecond(u)', "000")
    
    Dim s$
    d = sDate & "-" & sTime
    
    FormatDateEx = d
    
End Function

[Zvoni]

It's your own fault using "Now" from the Frontend....

Dim Cmd As cCommand
    Set Cmd = CnUser.CreateCommand("UPDATE pages SET pagedatetimemodified=DateTime('now','localtime'),pagethumbdirty=? WHERE pageguid=? AND pagebookguid=?")
    
    Cmd.SetBoolean 1, True
    Cmd.SetText 2, g_currentPage.Guid
    Cmd.SetText 3, tBook.Guid

    Cmd.Execute

[TysonLPrice]

It is against our standards to use Now(). A user can change the time on their computer and the can skew columns like "Time Updated". We get it off the server.

[wqweto]

It is against our standards to use Now(). A user can change the time on their computer and the can skew columns like "Time Updated". We get it off the server.

Btw, we are required by law to sync client local date/time with some external precise source of date/time and make sure audit info has correct (astronomical) times.

So throughout our code we always use a custom GetCurrentNow impl. instead and have built-in Now function redirected to produce both compile-time and run-time error when used erroneously:

Property Get GetCurrentNow() As Date
    If m_dCurrentStartDate = 0 Then
        GetCurrentNow = VBA.Now
    Else
        GetCurrentNow = DateAdd("s", TimerEx - m_dblCurrentStartTimer, m_dCurrentStartDate)
    End If
End Property

Property Get Now(ByVal dwDummy As Long) As Long
    Err.Raise vbObjectError, , "Use GetCurrentNow instead"
End Property

And we usually end up using DB server's date/time as source of truth

    Set rs = oCmd.Execute()
    If Not SetCurrentDateTimer(rs.Fields(0).Value, TimerEx) Then . . .

Public Function SetCurrentDateTimer(ByVal dDate As Date, dblTimer As Double, Optional Error As String) As Boolean
    m_dCurrentStartDate = dDate
    m_dblCurrentStartTimer = dblTimer
    '--- success
    SetCurrentDateTimer = True
End Function

Each DB request starts with a "test connection" query of SELECT GETDATE() or similar which if successful subsequently uses the result from the "test" to call SetCurrentDateTimer.

cheers,
</wqw>

[Zvoni]

It is against our standards to use Now(). A user can change the time on their computer and the can skew columns like "Time Updated". We get it off the server.

There is no "server" involved with OP.
He's using sqlite

[wqweto]

Btw, this seems to still work: http://vbnet.mvps.org/index.html?cod...cksynctime.htm

cheers,
</wqw>

[Arnoutdv]

Why not store the date (Now) as the float value?
It seems TS is using the text output of the Now method

[Zvoni]

Why not store the date (Now) as the float value?
It seems TS is using the text output of the Now method

Wrong question.
Why is he using "now" from the Frontend (with all pitfalls that entails), instead of letting sqlite do it for him?

[TysonLPrice]

There is no "server" involved with OP.
He's using sqlite

My point is using now() is against our standards and I think the reasons should be obvious. I said we get it off the server, not that the OP should. There are a number of ways not to rely on Now(). We do have group policies to keep people from doing certain things. People tend to try and find ways around that though.

[Zvoni]

My point is using now() is against our standards and I think the reasons should be obvious. I said we get it off the server, not that the OP should. There are a number of ways not to rely on Now(). We do have group policies to keep people from doing certain things. People tend to try and find ways around that though.

Ahh...OK. Got you

[tmighty2]

Btw: I was using "Now" just to keep track of when the user modified some data.
It is not critical data.

[gilman]

I don't know what the original problem is, but the function Now works fine.
The problem with your FormatDateEx function is that you are converting a string formated as SQLLite Date (YYYY-MM-DD HH:MM:SS.SSS), that not matches with your regional settings, to a VB6 Date type.
If you want to convert the strings contained in SQLLite format to VB6 Date type, and viceversa, you can use these functions:

' ----------------------------------------------------------------
' Procedure Name: SQLLiteDateFormat
' Purpose: Returns a string with value formated with SQLlite format for dates (YYYY-MM-DDDD HH:NN:SS.SSS) puts the miliseconds to 0
' Procedure Kind: Function
' Procedure Access: Public
' Parameter value (Date): The date to be formatted
' Return Type: String
' Author: Gilman
' Date: 06/04/2025
' ----------------------------------------------------------------
Public Function SQLLiteDateFormat(ByVal value As Date) As String
    'SQL Lite Date Format
    'YYYY-MM-DD HH:MM:SS.SSS
    SQLLiteDateFormat = Format(value, "YYYY-MM-DD HH:NN:SS.000")
End Function

' ----------------------------------------------------------------
' Procedure Name: SQLLiteDate
' Purpose: Returns the date contained in the string value formatted with SQLite Format(YYYY-MM-DD HH:MM:SS.SSS) the miliseconds are ignored
' Procedure Kind: Function
' Procedure Access: Public
' Parameter value (String): The string containing the formatted date
' Return Type: Date
' Author: Gilman
' Date: 06/04/2025
' ----------------------------------------------------------------
Public Function SQLLiteDate(ByVal value As String) As Date
    SQLLiteDate = DateSerial(Left(value, 4), Mid$(value, 6, 2), Mid$(value, 9, 2))
    SQLLiteDate = DateAdd("h", Mid$(value, 12, 2), SQLLiteDate)
    SQLLiteDate = DateAdd("n", Mid$(value, 15, 2), SQLLiteDate)
    SQLLiteDate = DateAdd("s", Mid$(value, 18, 2), SQLLiteDate)
End Function