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

Hello!

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

I add a column like this:

Code:

---

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:

Code:

---

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:

Code:

---

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:

Code:

---

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:

Code:

---

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:

Code:

---

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'):

Code:

---

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!

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.

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.

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

Happy to help :)

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

Code:

---

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

---

Querying

Code:

---

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

---

EDIT2:

Quote:

---

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

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:

Code:

---

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:

Code:

---

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:

Code:

---

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.

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...

Edit: I ran the following query:

Code:

---

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'"

---

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.

Quote:

---

Originally Posted by tmighty2

Edit: I ran the following query:

Code:

---

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

Thank you. RC6 uses 3.42.0.

Quote:

---

Originally Posted by 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.

---

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.

Quote:

---

Originally Posted by tmighty2

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)