3 Replies - 2745 Views - Last Post: 16 February 2012 - 06:42 AM Rate Topic: -----

#1 Apokio  Icon User is offline

  • D.I.C Regular

Reputation: 128
  • View blog
  • Posts: 481
  • Joined: 14-August 09

Sqlite date between question

Posted 15 February 2012 - 09:45 AM

I am storing dates in my sqlite database as TEXT in the format YYYY-MM-DD. When query for dates between certain dates I get some strange results. If I query the same day of two different months It doesn't return all the data between the two dates. This is the query I am using:

select * from game WHERE date BETWEEN '2012-1-15' AND '2012-2-15';


I am guessing that Sqlite doesn't know these are dates and I need to convert them somehow or somehow tell the query they are dates but I cannot seem to find an answer. I even looked my so called "Definitve Guide to Sqlite" and it has one little section telling me sqlite has no date storage and it can read dates in this format and julian and UNIX time an I already know that and i am storing them in the YYYY-MM-DD format. Anyhelp would be great.

Is This A Good Question/Topic? 0
  • +

Replies To: Sqlite date between question

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Sqlite date between question

Posted 15 February 2012 - 10:08 AM

I haven't worked with sqlite but have you tried adding a 0 before the month? like this : '2012-01-15'
Was This Post Helpful? 1
  • +
  • -

#3 Apokio  Icon User is offline

  • D.I.C Regular

Reputation: 128
  • View blog
  • Posts: 481
  • Joined: 14-August 09

Re: Sqlite date between question

Posted 15 February 2012 - 11:03 AM

Yeah, it does that I just forgot to put that in when I typed it up.
Was This Post Helpful? 0
  • +
  • -

#4 Apokio  Icon User is offline

  • D.I.C Regular

Reputation: 128
  • View blog
  • Posts: 481
  • Joined: 14-August 09

Re: Sqlite date between question

Posted 16 February 2012 - 06:42 AM

View PostnK0de, on 15 February 2012 - 11:08 AM, said:

I haven't worked with sqlite but have you tried adding a 0 before the month? like this : '2012-01-15'


You are right the dates have to be in the YYYY-MM-DD format. I overlooked/forgot this when reading about date storage and I am storing the values without the 0 before the single digit months and dates which is screwing stuff up. Now i have to write a function to update all the dates currently in the database to the correct format so I can search them properly.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1