18

I store dates as String in my database, in this format:

YYYY-MM-DD HH:MM:SS

which is one of supported formats (http://www.sqlite.org/lang_datefunc.html). Now I want to compare these stored dates (well, strings) with another dates. My dates are stored in column column_date, so I'm trying this:

SELECT  * FROM MyTable
WHERE 
(Date(column_date) >= Date (2000-01-01) AND Datetime(column_date) <= Datetime (2050-01-01))

As i read documentation, The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. so I suppose, I'm doing it right - I create date from stored string and compare it with date created from another strings.

But it doesn't work, even when column_date is date from this year, and as u can see the start and end dates are very benevolent. Tried also this (used datetime instead of date):

SELECT  * FROM MyTable
    WHERE 
    (datetime(column_date) >= Date (2000-01-01) AND datetime(column_date) <= Datetime (2050-01-01))

and this (use between instead of <= and >=)

SELECT  * FROM MyTable
    WHERE 
    (Date(column_date) between Date (2000-01-01) AND Datetime (2050-01-01))

and all other possible combinations. What the hell I'm doing wrong, am I stupid, or the documentaion lies, or I missed something very important? Trying to find solution few hours, but nothing works...

qkx
  • 2,383
  • 5
  • 28
  • 50

5 Answers5

22

if you are storing dates as strings in the format

YYYY-MM-DD HH:mm:ss

====> then your date field is a DateTime datatype

Thus you have to use such query

select * 
  from MyTable 
  where mydate >= Datetime('2000-01-01 00:00:00') 
  and mydate <= Datetime('2050-01-01 23:00:59')

you can also use the snippet given by @Joop Eggen with th between operator it's th same approche.

The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z" except that with BETWEEN, the x expression is only evaluated once. see sqlite3 docs

K_Anas
  • 31,226
  • 9
  • 68
  • 81
  • I don't believe that is accurate... from the SQLite docs "The date() function returns the date in this format: YYYY-MM-DD". The way I read it that means that even though the data is stored as a DateTime format, using `Date()` will return only the date portion of it. Is that not correct? – Barak May 31 '12 at 12:22
  • this works, but still cant understand this. In docs its clearly said, that date, or datetime return DATE. And my column is string. So how is possible, that u can compare String (=mydate) with date(date created from string 2000-01-01 00:00:00 with using datetime function)? Its not logical. Why u didn't apply Datetime also to my column, if its string? – qkx May 31 '12 at 12:29
  • so actually, for me this is logically right: Datetime(mydate string) <= Datetime(another date string). But this don't work and your code does work ;) Funny – qkx May 31 '12 at 12:32
  • SQLite does not have a storage class set aside for storing dates and/or times!! see the official doc http://sqlite.org/datatype3.html You are referring Sqlite to Sql that's what i think is confusing you sqlite don't support the date format what only does is storing a date as a string or an real – K_Anas May 31 '12 at 12:39
  • 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. And that what you have chosen so for you comparing dates is comparing strings!! – K_Anas May 31 '12 at 12:41
  • well i know, that sqlite dont have date datatype. But let me explain my thinking: I store string 'YYYY-MM-DD HH:MM:SS' to text column and name it mydate (but its string). So, how its possible to make this comparision: mydate >= Datetime('2000-01-01 00:00:00') when mydate is string (!!!) and the return of Datetime function is date (look docs http://www.sqlite.org/lang_datefunc.html right at the beginning). So actually u're doing this: string >= date. Why this works? Its strange for me...Comparing string and dates? – qkx May 31 '12 at 12:46
  • well, maybe i understand! It written in docs (http://www.sqlite.org/lang_datefunc.html): `he date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD.` But I think, they didn't write it 100% clearly, as many people will udnerstand sentence `The date() function returns the date in this format: YYYY-MM-DD.` like its returning date. But its returning string, they only wrote it in strange way... – qkx May 31 '12 at 12:50
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12021/discussion-between-qkx-and-k-anas) – qkx Jun 01 '12 at 09:28
  • i m storing in YYYY-MM-DD format but m not able to fetch record from db? – W I Z A R D Oct 25 '14 at 10:56
8
SELECT *
FROM MyTable
WHERE 
    column_date BETWEEN '2000-01-01 00:00:00' AND '2050-01-01 23:00:59'

Should do the trick. This only uses strings but you said that would be fitting.

The error was the missing time part or not doing a substr on the date part only. And then data/time conversion functions might give wrong things too.

Joop Eggen
  • 107,315
  • 7
  • 83
  • 138
  • this works...but how its possible? U're comparing strings with date function (between)? – qkx May 31 '12 at 12:06
  • I thought column_date was a string - VARCHARS? But a DATETIME would do too, as the standard *2000-01-01T00:0:00' and '2000-01-01 00:00:00' are convertible too. – Joop Eggen Jun 01 '12 at 09:16
  • can we check this date 01-01-2011 00:00:00? – CoronaPintu Jul 17 '13 at 06:36
  • 1
    @PintuCorna `'2011-01-01 00:00:00'` it should be. Or a `T` instead of the space, to adhere to the official ISO standard. Strangely enough dd-MM-yyyy was a standard (still used in the Netherlands) but the _neutral_ unique yyyy-MM-dd now is favoured. Makes me optimistic, of Esperanto and other endeavours. – Joop Eggen Jul 17 '13 at 09:14
  • BETWEEN is optimal as it can use ordered indices. BETWEEN DATE '...' one also sees and would be better typed. Time with LIKE. – Joop Eggen Oct 16 '15 at 08:02
3

I think your issue is here...

AND Datetime(column_date) <= Datetime (2050-01-01))

Why are you using Datetime here instead of Date?

Try this:

SELECT  * FROM MyTable WHERE (Date(column_date) >= Date (2000-01-01) AND Date(column_date) <= Date (2050-01-01))
Barak
  • 16,318
  • 9
  • 52
  • 84
2

Date filed in the SQLite table will be TEXT and date data should be stored as "2012-12-31 00:12:00" format that comparison dose not give you trouble and if we consider that fields are date_from and date_to and we are storing current date at to_date then we should get the current date as bellow and

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String to_date = sdf.format(new Date());

and the SQL query should be 

    crs = db.rawQuery(
      "SELECT _id, date_from, date_to, done_or_not, list_name " +
      "FROM list_tbl " +
      "WHERE " +
      "date_from <= Datetime('"+to_date+"') AND date_to >= Datetime('"+to_date+"')", null);
Hasanur
  • 146
  • 7
0

Compare dates (stored as string) in android SQLite database. Use date in 12/07/2015 format and use a query like this:

SELECT * FROM tableName WHERE Date >=('12/07/2015') and appointmentDate <('13/07/2015')";

This works for me.

Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339
user3571876
  • 1