SQLite weird date result

Targets macOS
1

Greetings.

So I have an app that does some processing and while doing an Insert in the database on the end of processing it seems that the date is super weird, instead of the right one.

Here is a part of the code :

[code]Dim nD As New Date

Dim sql As String

sql = “INSERT INTO users (name, pass, type, created) VALUES (‘c’, ‘a’, ‘b’, " + nD.SQLDate + " )”

Self.SQLExecute(sql)

Self.Commit
[/code]

So based on the

Dim nD As New Date

I should get a date of 2019-02-21 and the time but instead in the record side I get

just [quote]1996 [/quote] without any other details.

Any idea if this is a bug or something wrong with XOJO or current SQLite version ?

As usual , Latest OSX , Latest XOJO

Thanks.

2

You Statement is missing quotation marks around the Date.

Here’s a Prepared Statement Variant of your Code:

[code]Dim nD As New Date
Dim p As PreparedSQLStatement

p = db.Prepare(“INSERT INTO users (name, pass, type, created) VALUES (?, ?, ?, ?)”)

p.Bind(0, “c”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(1, “a”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(2, “b”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(3, nd.SQLDate, SQLitePreparedStatement.SQLITE_TEXT)

p.SQLExecute[/code]

3

[quote=425681:@Sascha S]You Statement is missing quotation marks around the Date.

Here’s a Prepared Statement Variant of your Code:

[code]Dim nD As New Date
Dim p As PreparedSQLStatement

p = db.Prepare(“INSERT INTO users (name, pass, type, created) VALUES (?, ?, ?, ?)”)

p.Bind(0, “c”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(1, “a”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(2, “b”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(3, nd.SQLDate, SQLitePreparedStatement.SQLITE_TEXT)

p.SQLExecute[/code][/quote]

I guess just doing the same think so it worked.

Thanks again.

4

[quote=425680:@Aurelian Negrea]So based on the

Dim nD As New Date

I should get a date of 2019-02-21 and the time but instead in the record side I get

1996[/quote]
Interesting that in this case, without the correct quotes, the subtract operation is performed:
2019-02 = 2017
2017-21 = 1996

Just for reference, I think the correct sql string is:

sql = "INSERT INTO users (name, pass, type, created) VALUES ('c', 'a', 'b', '" + nD.SQLDate + "' )"

but you should use prepared statements as mentioned by Sascha.