RC6: Trouble with DATETIME NOT NULL DEFAULT 0 after ALTER TABLE in VB6

[tmighty2]

Hello!

I’ve run into a confusing situation with RC6 SQLite and VB6.

I add a column like this:

cn.Execute "ALTER TABLE foo ADD COLUMN lastcheckdate DATETIME NOT NULL DEFAULT 0"

Expectation: all existing rows should then have lastcheckdate = 0.

But when I test it:

Dim nCmdsel As cSelectCommand
Set nCmdsel = m_Cn.CreateSelectCommand("SELECT lastcheckdate, rowid FROM foo WHERE lastcheckdate=?")

nCmdsel.SetDate 1, 0

Set r = nCmdsel.Execute
Debug.Print r.RecordCount   ' --> returns 0

This approach yields the same result:

Dim dt As Date
Dim nCmdsel As cSelectCommand
Set nCmdsel = m_Cn.CreateSelectCommand("SELECT lastcheckdate, rowid FROM foo WHERE lastcheckdate=?")

nCmdsel.SetDate 1, dt

Set r = nCmdsel.Execute
Debug.Print r.RecordCount   ' --> returns 0

Whereas:

Set r = m_Cn.OpenRecordset("SELECT lastcheckdate, rowid FROM foo LIMIT 1")
Debug.Print r.RecordCount   ' --> returns 1

So rows are there, but nothing matches WHERE lastcheckdate=0.

Even more strange:

Debug.Print "value: " & ChrW(34) & r.Fields("lastcheckdate").Value & ChrW(34)
' --> value: ""

Debug.Print IsEmpty(r.Fields("lastcheckdate").Value)
' --> True

So VB6 shows the field as Empty when hovering over the variable.

But then:

Dim iLong As Long
iLong = -1
iLong = CLng(r.Fields("lastcheckdate").Value)
Debug.Print iLong   ' --> 0

No error is raised, and it silently converts to 0.

From pragma_table_info('foo'):

pragma_table_info('foo'):
name: lastcheckdate
type: DATETIME
notnull: 1
dflt_value: 0

So on schema level it looks correct.

Problem:
Existing rows after the ALTER TABLE do not actually contain 0. They behave like Empty/Null/"" in VB6, which cannot be matched with SetDate 1, 0.

Questions:

Is this expected SQLite behavior (default only applies to new inserts, not existing rows)?

If yes, what’s the recommended RC6/VB6 way to normalize old rows (so that they really contain 0 and can be queried with SetDate)?

Thanks a lot for any advice!

[tmighty2]

Edit:

I have come to the conclusion that UnixTimeStamp is better suited for DBs, and I will help myself by converting DATETIME to SQLITE INTEGER.

[jpbro]

The CSelectCmd SetDate method is converting dates to YYYY-MM-DD HH:NN:SS format, and 0 is a special case that is being converted '' (to indicate no date). If your DB is using 0 (integer) as the default date instead of empty/'' then try using the SetInt32 method instead of SetDate. NOTE that I haven't tested this, but I think it should work.

[tmighty2]

I believe it works, thank you very much!!!

[jpbro]

Happy to help

[Zvoni]

And this is exactly the Reason why i don't use the DateTime-Type in SQlite, since it translates to numeric Affinity.

And a DateTime of Value "0" is -4713-11-24 12:00:00 BC for SQLite

Use TEXT and its representation of Date/Time, and you can set a Default Value to your hearts content

EDIT:
FWIW, there is nothing wrong with allowing NULL for a DateTime-Field (if you insist on keeping DateTime-Datatype
You'd just have to Change your code to

cn.Execute "ALTER TABLE foo ADD COLUMN lastcheckdate DATETIME"

Querying

SELECT lastcheckdate, rowid FROM foo WHERE COALESCE(lastcheckdate, 0)=?

EDIT2:

Edit:

I have come to the conclusion that UnixTimeStamp is better suited for DBs, and I will help myself by converting DATETIME to SQLITE INTEGER.

Don't!
You'll just set yourself up to run into the Year 2038-Bug.
Nevermind the Math involved to handle LeapYears

EDIT3:
And remember: SQLite interprets DateTimes as UTC if no "localtime"-modifier is used

[tmighty2]

I am going with SQLite and datetime.
I never work with strings when I deal with date.

However, I somehow managed to get this crazy result:

Cols in "jscodes" according to query on pragma_table_info:
"datum", ["cid": "0"], ["type": "DATETIME"], ["notnull": "1"], ["dflt_value": "0"], ["pk": "0"]
"title", ["cid": "1"], ["type": "TEXT"], ["notnull": "1"], ["dflt_value": "''"], ["pk": "0"]
"code", ["cid": "2"], ["type": "TEXT"], ["notnull": "1"], ["dflt_value": "''"], ["pk": "0"]

    Dim r As cRecordset
    Set r = CnEx.OpenRecordset("SELECT rowid,datum,code FROM jscodes ORDER BY datum DESC LIMIT 10")

    Dim dPrevRecord As Date
    dPrevRecord = 0
    
    Dim k&
    For k = 1 To r.RecordCount

        Dim dNowRecord As Date
        dNowRecord = r!datum
        
        Debug.Print "datum " & k & ", rowid: " & r!RowID & ": " & r!datum
        
        If dPrevRecord <> 0 Then
            If dPrevRecord < dNowRecord Then
                Debug.Print "Something is wrong. Why does sqlite think that " & dPrevRecord & " is bigger than " & dNowRecord & "?"
            End If
        End If
        
        dPrevRecord = dNowRecord
        
        r.MoveNext
    Next

     Set r = CnEx.OpenRecordset("SELECT rowid,datum,code FROM jscodes ORDER BY datum DESC LIMIT 10")
    
    Dim k&
    For k = 1 To r.RecordCount
        
        dNowRecord = r!datum
        If dPrevRecord <> 0 Then
            If dPrevRecord > dNowRecord Then
                Debug.Print "Something is wrong. Why does sqlite think that " & dNowRecord & " is bigger than " & dPrevRecord & "?"
            End If
        End If
        
        dPrevRecord = dNowRecord
        Debug.Print "datum " & k & ", rowid: " & r!RowID & ": " & r!datum
        r.MoveNext
    Next

My code results in the following debug messages:

datum 1, rowid: 394: 24.09.2025 19:55:02
datum 2, rowid: 393: 24.09.2025 19:34:41
datum 3, rowid: 395: 24.09.2025 20:07:54
Something is wrong. Why does sqlite think that 24.09.2025 19:34:41 is bigger than 24.09.2025 20:07:54?
datum 4, rowid: 396: 24.09.2025 20:07:54
datum 5, rowid: 397: 24.09.2025 20:07:54
datum 6, rowid: 398: 24.09.2025 20:07:54
datum 7, rowid: 399: 24.09.2025 20:07:54
datum 8, rowid: 400: 24.09.2025 20:07:54
datum 9, rowid: 401: 24.09.2025 20:07:54
datum 10, rowid: 392: 18.09.2025 21:27:47

I had converted the column "datum" from "TEXT to "DATETIME" using this INSERT statement on a newly created table:

SELECT COALESCE([datum],0)

I know what Schmidt would say. He would cite my line where "I never deal with strings..." and he would write "Sure you do!".

However, I am unsure how to solve this problem.

[tmighty2]

Hmmm, maybe Schmidt would write "You can however use INT64 to work around the year-2038 limit", and he might add a little thought about code in the year 2038...

[tmighty2]

Edit: I ran the following query:

Set r = CnEx.OpenRecordset("SELECT rowid, datum, typeof(datum) AS datum_type, quote(datum) AS raw_value FROM jscodes ORDER BY datum DESC")

And I got this:

Record 401, field "rowid, value: 200"
Record 401, field "datum, value: 03.09.2025 20:46:09"
Record 401, field "datum_type, value: text"
Record 401, field "raw_value, value: '03.09.2025 20:46:09'"

[Arnoutdv]

I either use a plain integer for the date (the integer part of a VB6) date or a float and store the complete date (double) variable of a VB6 date.

[Zvoni]

Edit: I ran the following query:

Set r = CnEx.OpenRecordset("SELECT rowid, datum, typeof(datum) AS datum_type, quote(datum) AS raw_value FROM jscodes ORDER BY datum DESC")

And I got this:

Record 401, field "rowid, value: 200"
Record 401, field "datum, value: 03.09.2025 20:46:09"
Record 401, field "datum_type, value: text"
Record 401, field "raw_value, value: '03.09.2025 20:46:09'"

Because SQLite DOESN'T prevent you to insert a text into a Numeric column.
Use STRICT-Option for your CREATE TABLE statement
see: https://www.sqlite.org/stricttables.html
Needs sqlite-engine 3.37 - check which version is used in RC6

[tmighty2]

Thank you. RC6 uses 3.42.0.

[tmighty2]

I either use a plain integer for the date (the integer part of a VB6) date or a float and store the complete date (double) variable of a VB6 date.

Can you or Zvoni spoke out against UnixTime tell me why using SQLITE integer and UnixTime (and allowing it to be negative in order to deal with dates < 1980) might be a bad design decision?

I must say that I wonder why this is the not the standard approach.

[Zvoni]

Can you or Zvoni spoke out against UnixTime tell me why using SQLITE integer and UnixTime (and allowing it to be negative in order to deal with dates < 1980) might be a bad design decision?

I must say that I wonder why this is the not the standard approach.

Because using ANY Numeric DataType (Double/Float or Integer) you run into an eventual Overflow when reaching the Max-Value (Year 2038-Bug for Unixtime)
Using ISO8601-Text-Representation of DateTimes in SQLite you will never run into an Overflow.
Nevermind, that storing Date(Times) in Numeric Format will always need a conversion for Display resp. Text-Representation is still "readable" if you have to go into the Database directly (e.g. with DB Browser for SQLite)