CODEWITHSUNDEEP
javasql
Roger
Roger

Reputation: 4259

SQLite problem with dates

In my SQLiteDb, I have dates stored as text in the following format:

3-1-2011
3-5-2011
3-15-2011

I know... not ideal. Anyways, I'm pulling data from the table using WHERE date BETWEEN date1 AND date2. But it's not working properly. Presumably because it sees the dates as:

        3-1-2011
        3-10-2011
        3-11-2011
        ...
        3-2-2011
        3-20-2011
        3-21-2011
        ...

Without modifying my table and updating all values, is there a way to fix this?

Upvotes: 0

Views: 140

Answers (1)

swissben
swissben

Reputation: 1077

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

SQLite Datatypes Description

Have a look at this additional helpful information : Date And Time Functions

Upvotes: 1

Related Questions