CODEWITHSUNDEEP
androidsqlitedateandroid-studioandroid-sqlite
Jeorge
Jeorge

Reputation: 83

Date in SQLite not working

Why when i execute my code it return all dates of the table

code

    Strign raw[] = {"id","date"}
    String args[] ={"2017-6-24"} 
    Cursor cursor = database.query("database1", raw , date + "<=?"
                        , args, null, null, null);

Things that i have tried but have not worked:

1) i tried put "date(example date)" in my code

    Strign raw[] = {"id","date"}
    String args[] ={"date(2017-6-24)"} 
    Cursor cursor = database.query("database1", raw , "date("+date + ")<=?"
                        , args, null, null, null);

2) 

    Strign raw[] = {"id","date"}
    String args[] ={"2017-6-24"} 
    Cursor cursor = database.rawQuery("SELECT * FROM database1 "+ 
                                       "WHERE date<="+"2017-6-24",null);

EDIT

In my app i need return all dates that are previous that one date specific i.e if i have in my table of date this 

     | ID |   DATE   |
     -----------------
     |  1 | 2018-6-21|
     |  2 | 2015-5-12|
     |  3 | 2013-3-17|
     |  4 | 2017-2-13|

and i input in my app the date '2017-6-24' i need return all dates that are previous that it i.e i need get

     | ID |   DATE   |
     -----------------
     |  2 | 2015-5-12|
     |  3 | 2013-3-17|
     |  4 | 2017-2-13|

but when i try do it i get all dates of the table

Upvotes: 4

Views: 2750

Answers (1)

MikeT
MikeT

Reputation: 57063

When you use the original you are effectively issuing the query :-

SELECT * FROM database1 WHERE date(date)<='random date';

i.e. it will return all rows where date (e.g. string 2018-01-01) is less than or equal to the string random date. Therefore as r is greater then 2 (or any numeric as a character) so it will return all rows.

More specifically:-

  • random is not a function that returns a random number, nor does random date

Attempt 1 will effectively issue the query :-

SELECT id,date FROM database1 WHERE date(date)<='date(random date)';

i.e. as above but the first character will be d

Attempt 2 will effectively issue the query :-

SELECT * FROM database1 WHERE date<=random date;

which would fail with a syntax error like near "date": syntax error

More specifically:-

  • random is not a function that returns a random number, nor does random date return a random date. random() is one of SQLite's core functions that can be used to return a random 64bit signed integer.

    • any argument passed via the second parameter of rawQuery or as the fourth paramter of query will result in the value being enclosed as a string so SQLite functions cannot be passed as arguments as they will be enclosed as an treated as a string.

It would appear that you want something based upon :-

SELECT * FROM database1 WHERE date<=date(random());
  • Note the above assumes that instead of Strign, String is used as the type for the raw variable.

Not that the above will work. To cut a long story short (i.e. having to use datetime() and also using 'uniepoch' to use an integer and a few other manipulations) which would then result in a vast range of dates from 1970 through to dates well past the lifetime of people even born today. You probably want a date within a relatively short defined period to get any rows returned.

So say you want a random set of rows based upon a date within a year from the current date, then you could have :-

SELECT * FROM database1 WHERE  date <= date('now','+'||(ABS(random())%365)||' DAYS');

What this is doing is :-

  • Generating a random number which has a massive range, negative and positive values, then
    • Using the ABS(n) function to return a positive value.
    • Returning the modulus (remainder) after dividing that number by 365 (1 years worth of days).
    • Adding that number of days to the current date to come up with a date within a year from now as the right argument of the WHERE clause.

This could be used using :-

String raw[] = {"id","date"}
Cursor cursor = database.query("database1", raw , date <= date('now','+'||(ABS(random())%365)||' DAYS')
                    , null, null, null, null);
  • Note, this is not necessarily specifically what you want, you don't actually clarify your requirements. Rather it is an example of how you can get a random date which may or may not be suitable (e.g. it could be that you want a date a year before now, in which case the + could be changed to - ).

Edit 1

re :-

NOTE: When i said "random date" i refer to any date that i put inside the parentheses not to the function 'random()', for example i put 8-6-2018 where is the "random date" i.e date<=(8-6-2018)

Dates should not be in that format, dates should be (using the most likely suitable format) yyyy-mm-dd e.g. 2018-06-08 (likewise they should be stored like this in the database),

SQLite expects dates in a limited number of formats as per

SQL As Understood By SQLite - Date And Time Functions -Time Strings

using SELECT * FROM database1 WHERE date <=(8-6-2018); is effectively saying SELECT * FROM database WHERE date <= -2016 ( i.e 8 less 6 less 2018, which equates to 2 less 2018).

You will encounter numerous issues is you do not change to a recognised format. Not that it's impossible but using 8-6-2018 (and the permutations of formats i.e. 8-6-2018 (8 characters), 10-6-2018 now 9 characters, and 10-10-2018 (10 characters)) would require complexities in converting to a usable format.

e.g. you'd have to use something like :-

WITH 

 selection_date(sdate) AS ( SELECT '8-6-2018'), --<<<<<<<< selection value

 converted_selection_date(sdate,converted_selection) AS (
        SELECT DISTINCT
            sdate,
            CASE
            WHEN length(sdate) = 8 THEN
                substr(sdate,5,4)||'-0'||substr(sdate,3,1)||'-0'||substr(sdate,1,1)
            WHEN (length(sdate) = 9 AND substr(sdate,2,1) = '-') THEN -- d-mm-yyyy
                substr(sdate,6,4)||'-'||substr(sdate,3,2)|'-0'||substr(sdate,1,1)
            WHEN (length(sdate) = 9 AND substr(sdate,3,1) = '-') THEN -- dd-m-yyyy
                substr(sdate,6,4)||'-0'||substr(sdate,4,1)||'-'||substr(sdate,1,2)
            WHEN (length(sdate) = 10 AND substr(sdate,3,1) = '-') THEN -- dd-mm-yyyy
                substr(sdate,7,4)||'-'||substr(sdate,4,2)||'-'||substr(sdate,1,2)
            ELSE sdate||'----'||length(sdate)
        END AS converted_selection
        FROM selection_date
    ),

 converted_date_column(rid,converted_date) AS (
    SELECT rowid AS rid, 
        CASE 
            WHEN (length(date) = 8 AND substr(date,2,1) = '-') THEN -- d-m-yyyy 
                substr(date,5,4)||'-0'||substr(date,3,1)||'-0'||substr(date,1,1)
            WHEN (length(date) = 9 AND substr(date,2,1) = '-') THEN -- d-mm-yyyy
                substr(date,6,4)||'-'||substr(date,3,2)|'-0'||substr(date,1,1)
            WHEN (length(date) = 9 AND substr(date,3,1) = '-') THEN -- dd-m-yyyy
                substr(date,6,4)||'-0'||substr(date,4,1)||'-'||substr(date,1,2)
            WHEN (length(date) = 10 AND substr(date,3,1) = '-') THEN -- dd-mm-yyyy
                substr(date,7,4)||'-'||substr(date,4,2)||'-'||substr(date,1,2)
            ELSE date
            END AS converted_date
    FROM argent
    )
    SELECT date FROM argent, converted_selection_date WHERE date <= converted_selection
;

Edit 2

re :-

The easiest solution is to always use the 10 character format i.e. yyyy-mm-dd rather than a 8-10 character format e.g.

instead of 2017-6-24 or 2017-6-1 use 2017-06-24 or 2017-06-01. (i.e leading 0's for months and days less than 10)

In which case the original and 2) will work as they are.

1) to work could be :-

String raw[] = {"id","date"}
String args[] ={"random date"} 
Cursor cursor = database.query("database1", raw , "date(date)<=date(?)"
                    , args, null, null, null);

i.e. only the value itself is the argument the rest of the where clause is hard coded and thus then not quoted.

Although simpler than the example conversion above, if the format is kept as the 8-10, then you'd still need to do some conversion like the above, so as to not get unwanted results.

e.g. if the selection date were 2017-2-1 then 2017-10-1 would come out as being less using the original and 2) (1 wouldn't work at all well). because the string 2017-1.... is less than the string 2017-2....

Upvotes: 3

Related Questions