Android Question Cant save a date to Sqlite

Discussion in 'Android Questions' started by Alberto Michelis, Jul 15, 2015.

  1. Alberto Michelis

    Alberto Michelis Active Member Licensed User

    I dont get it,

    I create a table with:

    Code:
    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:

    Code:
    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
     
  2. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    Dates are stored as long values,number of ticks.
     
  3. Alberto Michelis

    Alberto Michelis Active Member Licensed User

    Does not worked

    Code:
    MyDate = DateTime.Now
    gets saved as 1899-12-30
     
  4. Mahares

    Mahares Well-Known Member Licensed User

    Code:
    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
     
    lemonisdead, mangojack and NJDude like this.
  5. Alberto Michelis

    Alberto Michelis Active Member Licensed User

    ok, thanks

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

    Mahares Well-Known Member Licensed User

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

    LucaMs Expert Licensed User

    I agree totally (and who cares? :D)
     
  8. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    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.
     
    DonManfred and lemonisdead like this.
  9. LucaMs

    LucaMs Expert Licensed User

    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 :D;
    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.
     
  10. DonManfred

    DonManfred Expert Licensed User

    Code:
    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)
     
    Last edited: Jul 16, 2015
    Jmu5667 likes this.
  11. Jmu5667

    Jmu5667 Well-Known Member Licensed User

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

    DonManfred Expert Licensed User

    you are right.
    I was talking about storing the value as STRING in the Database
     
    Jmu5667 likes this.
  13. Mahares

    Mahares Well-Known Member Licensed User

    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.
    Code:
    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"))
     
  14. Jmu5667

    Jmu5667 Well-Known Member Licensed User

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

    LucaMs Expert Licensed User

    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?
     
    Last edited: Jul 17, 2015
    Mahares likes this.
Loading...