Data retrieving from sqlite DB between two dates - using objective c

Question

I am using the below query with date filtering, but I am getting wrong result.

SELECT * FROM TRANSACTIONSHISTORY 
WHERE DATE > "29-01-2015 12:00:00" 
AND DATE < "30-01-2015 00:00:00" AND USERID=abc

I am getting result with date column with value of 29-Jan-2016 records, what am I missing here, can any one help me to get out of this value.

Answer 1

You have cast your string to Date

SELECT * FROM TRANSACTIONSHISTORY WHERE DATE between Datetime('29-01-2015 12:00:00') and Datetime('30-01-2015 00:00:00') AND USERID=abc

Answer 2

Try this first try without Time,after that try date and time both , Hope i will work for you

SELECT TRANSACTIONSHISTORY
FROM SHIPMENT 
WHERE DATE 
BETWEEN '11-15-2010' 
AND '30-01-2015'
// you can try this one  also
SELECT * FROM TRANSACTIONSHISTORY WHERE DATE BETWEEN "2011-01-11" AND "2011-8-11"

Answer 3

The first answer is exactly what you need. What you did in your code would be comparing strings using ASCII values.

I would recommend you to use the linux time stamps like: 1453818208, which is easier to save and compare. In addition, it can always be translated to human-readable dates like: 29-01-2015 12:00:00.

SELECT * FROM TRANSACTIONSHISTORY WHERE DATE > "29-01-2015 12:00:00" AND DATE < "30-01-2015 00:00:00" AND USERID=abc

I hope this helps you :)

Answer 4

The date format in your SQL will not work because SQLite doesn't have a native datetime type, so it's generally stored either as a string, in YYYY-MM-DD HH:MM:SS.SSS format, or as an numeric value representing the number of seconds since 1970-01-01 00:00:00 UTC. See date and time types on SQLite.org. Note that if you're using the string representation that the sequence is year, month, day (which, when sorting/querying this string field, the this alphanumeric string will sort correctly by year first, then month, and then day, which is critical when doing queries like yours).

If you really stored dates in the database as a string in the DD-MM-YYYY HH:MM:SS format, you should consider changing the format in which you saved the values into one of the approved date formats. It will make the date interactions with the database much, much easier, allowing queries like the one you asked for (though, obviously, with DD-MM-YYYY replaced with YYYY-MM-DD format).