Android Question Cant save a date to Sqlite

[Alberto Michelis]

I dont get it,

I create a table with:

CREATE TABLE [results] ( SqliteDate DATE );

and is created ok, then I assume the date field type exists

now Im trying to save a date and no matter the format it always gets saved wrong

I tryed with yyyy-MM-dd and yyyy-dd-MM:

Dim MyDate As String
MyDate= DateTime.GetYear(DateTime.Now)&"-"&DateTime.GetMonth(DateTime.Now)&"-"&DateTime.GetDayOfMonth(DateTime.Now)
SQL1.ExecNonQuery("INSERT Or REPLACE INTO results VALUES ('"&MyDate&"')

and its always geting saved as 1989-12-30 and so on

How can I do it??

Thanks

 

[Jmu5667]

Dates are stored as long values,number of ticks.

 

[Alberto Michelis]

Does not worked

            MyDate = DateTime.Now

gets saved as 1899-12-30

 

[Mahares]

Dim MyQuery As String    ="CREATE TABLE results (SqliteDate TEXT)"
SQL1.ExecNonQuery(MyQuery)
Dim OrigFormat As String=DateTime.DateFormat
DateTime.DateFormat="yyyy-MM-dd"
Dim MyDate As String =DateTime.Date(DateTime.Now)
SQL1.ExecNonQuery2("INSERT Or REPLACE INTO results VALUES (?)",Array As String(MyDate)
DateTime.DateFormat=OrigFormat

 

[Alberto Michelis]

ok, thanks

but why does sqlite allow me to set a DATE field ? weird...

 

[Mahares]

Yes you can store your SqliteDate field as DATE. All the below queries yield the same result based on the query you used: 2015-7-16
"CREATE TABLE results (SqliteDate INTEGER)"
"CREATE TABLE results (SqliteDate DATE)"
"CREATE TABLE results (SqliteDate DATETIME)"
"CREATE TABLE results (SqliteDate TIME)"
"CREATE TABLE results (SqliteDate TEXT)"
I prefer to store it as 2015-07-16 and use any of the above, so you can easily index, sort descending or ascending if you have to.
The SQlite documentation explains the data types and classes in detail:
https://www.sqlite.org/datatype3.html
https://www.sqlite.org/lang_datefunc.html
By the way, would you please change your age in your profile. It is more proper not to give personal information than put the wrong information.

 

[LucaMs]

I prefer to store it as 2015-07-16 and use any of the above, so you can easily index, sort descending or ascending if you have to.

I agree totally (and who cares? )

 

[Jmu5667]

Does not worked

            MyDate = DateTime.Now

gets saved as 1899-12-30

If you defined the date field in the DB as long value then using the ticks you can do all your searching etc. Using this method negates the need for different date formats etc. I currently use this method and it works perfectly. Storing a date format in a string field will work but may cause you problems if date formats are different to that in which you have stored them.

Try the storing the ticks.

Regard

John.

 

[LucaMs]

If you defined the date field in the DB as long value then using the ticks you can do all your searching etc. Using this method negates the need for different date formats etc. I currently use this method and it works perfectly. Storing a date format in a string field will work but may cause you problems if date formats are different to that in which you have stored them.

Try the storing the ticks.

Regard

John.

You're right about the string date format, but:

a) if you store dates always in one format, yyyy/MM/dd, you can not go wrong;
b) your eye can see the date value ;
c) string functions are much simpler than other that not even work;
d) you have no limits, you can set a date to 0001/01/01

Downside: you can not store time so.

 

[DonManfred]

Downside: you can not store time so.

yyyy/MM/dd HH:mm
           ^^^^^ not sure if this is right...

Just use a longer string and it will work. Even for sorting

But i prefer to store the long value (ticks)

 

[Jmu5667]

yyyy/MM/dd HH:mm
           ^^^^^ not sure if this is right...

Just use a longer string and it will work. Even for sorting

But i prefer to store the long value (ticks)

Date/time are stored as a numeric value anyway otherwise they would not work.

 

[DonManfred]

Date/time are stored as a numeric value anyway otherwise they would not work.

you are right.
I was talking about storing the value as STRING in the Database

 

[Mahares]

Date/time are stored as a numeric value anyway otherwise they would not work.

That is not correct.
Check out this complete sample below. The select query will show all records except for 2015/05/15 16:10 because it is outside the date range selected. Also, you can sort the records even if the format of the date is as shown.

Dim MyQuery As String    ="CREATE TABLE results (SqliteDate TEXT)"
SQL1.ExecNonQuery(MyQuery)

MyQuery="INSERT Or REPLACE INTO results VALUES (?)"
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/05/10 19:45"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/01/10 10:45"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/03/10 17:45"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/05/15 16:10"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/05/15 09:50"))

MyQuery="SELECT * FROM results WHERE SqliteDate BETWEEN ? AND ? "
Cursor1=SQL1.ExecQuery2(MyQuery,Array As String("2015/01/10 10:10","2015/05/15 10:10"))

 

[Jmu5667]

That is not correct.
Check out this complete sample below. The select query will show all records except for 2015/05/15 16:10 because it is outside the date range selected. Also, you can sort the records even if the format of the date is as shown.

Dim MyQuery As String    ="CREATE TABLE results (SqliteDate TEXT)"
SQL1.ExecNonQuery(MyQuery)

MyQuery="INSERT Or REPLACE INTO results VALUES (?)"
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/05/10 19:45"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/01/10 10:45"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/03/10 17:45"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/05/15 16:10"))
SQL1.ExecNonQuery2(MyQuery,Array As String("2015/05/15 09:50"))

MyQuery="SELECT * FROM results WHERE SqliteDate BETWEEN ? AND ? "
Cursor1=SQL1.ExecQuery2(MyQuery,Array As String("2015/01/10 10:10","2015/05/15 10:10"))

You are using a TEXT field, bad idea, see DonManfred post.

 

[LucaMs]

You are using a TEXT field, bad idea, see DonManfred post.

I remember my war against SQLite functions when I need to use them to filter by date on fields saved as number.


What if I want to get only records in which a Date is April?

Using texts, like "2015/04/25", you can easily use string functions to get that 04; how you do it using Date fields saved as ticks?