Warning about Now() function / region time date format change
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:
Code:
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:
Code:
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:
Code:
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:
Code:
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
Re: Warning about Now() function / region time date format change
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:
Code:
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
Re: Warning about Now() function / region time date format change
It's your own fault using "Now" from the Frontend....
Code:
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
Re: Warning about Now() function / region time date format change
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.
Re: Warning about Now() function / region time date format change
Quote:
Originally Posted by
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.
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:
Code:
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
Code:
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>
Re: Warning about Now() function / region time date format change
Quote:
Originally Posted by
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.
There is no "server" involved with OP.
He's using sqlite
Re: Warning about Now() function / region time date format change
Re: Warning about Now() function / region time date format change
Why not store the date (Now) as the float value?
It seems TS is using the text output of the Now method
Re: Warning about Now() function / region time date format change
Quote:
Originally Posted by
Arnoutdv
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?
Re: Warning about Now() function / region time date format change
Quote:
Originally Posted by
Zvoni
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.
Re: Warning about Now() function / region time date format change
Quote:
Originally Posted by
TysonLPrice
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
Re: Warning about Now() function / region time date format change
Btw: I was using "Now" just to keep track of when the user modified some data.
It is not critical data.
Re: Warning about Now() function / region time date format change
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:
Code:
' ----------------------------------------------------------------
' 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
