I am comparing time in android sqlite database , here is my table strcutre in the screen shots,
actually i have two time like 8:00 and 7:30 (basically these are emplyee shift timings) in the string and
i have colum c_TIME in my db and i want all the records who lies in b/w my two time i.e.(8:00 and 7:30) from c_TIME.
i have used this quey but it is not working , please anyone guide me what i am doing wrong
WHERE strftime('%H','08:00') > strftime('%H', c_TIME) AND
time('08:00') < strftime('%H', c_TIME) AND
STATE = 'IN'
The following should work:
SELECT * FROM ATTENDANCE WHERE strftime('%H:%M:%S', c_date) > '07:30:00' AND strftime('%H:%M:%S', c_date) < '08:00:00' AND STATE = 'IN'
Instead of using the
c_TIME field, you should use
c_date. Actually, I don't see any value in keeping around a column which stores the time separately.
strftime returns a string based on some sort of datetime/timestamp input. The output from
strftime('%H:%M:%S', c_date) should have the form
HH:MM:SS, and this string can be compared lexicographically against
07:30:00. The comparison should still work, because for strings of the same length it should still sort numerically.