What is the Sqlite SQL statement to find todays records only?

Question

Lets just say I have a table with people's name and their birthday (date and time). I want to be able to find people's birthday (date and time) for today using the following SQL for SQLite.

SELECT * FROM Btable WHERE Bday = date('yyyy-MM-dd')

However, it is always returning with nil dataset. Do I need to also include the time as well?

What is exactly wrong with my SQL statement? If so, what is the correct SQL statement for querying for todays date.

UPDATE:

Here is my actual SQL statement using BETWEEN logic:

SELECT * 
FROM Alarms 
WHERE ALARMSTATE IN (0,1,2) 
  AND ALARMPRIORITY IN (0,1,2,3,4) 
  AND ALARMGROUP IN (0,1,2,3,4,5,6,7) 
  AND ALARMTIME BETWEEN date(2012-08-02) 
                    AND date(2012-08-3) 
ORDER BY ALARMTIME DESC

Birthday was not in the same year. You probably want to compare day and month, not years. — Nikola Markovinović, Aug 02 '12 at 14:23
@NikolaMarkovinović, Yea, it was a bad example, but you got the idea. :) I could have said that the table is an Alarm Log and I wanted to find all the Alarm Log for today. — ThN, Aug 02 '12 at 14:30
;-D In that case you need to either truncate `BDay` to date only or, better, filter where BDay >= my_date and BDay < (my_date + 1 day). unfortunately I don't use sqlite so I'm unable to provide exact syntax. — Nikola Markovinović, Aug 02 '12 at 14:34
@NikolaMarkovinović, I also tried the BETWEEN logic very similar to what you said in your previous comment. It also returned Nil Dataset. — ThN, Aug 02 '12 at 14:38
With datetime columns you are better of using >= and < than between. This avoids resolution problems (do I deduct millisecond? or 3? to be able to use between). Unfortunately Sql Fiddle is not working ATM so I cannot prepare some example. Could you please post your between query? — Nikola Markovinović, Aug 02 '12 at 14:42
Assuming that date format is correct and there are records corresponding to additional criteria I'd say it is fine. If you remove date filter and get your rows, format is probably bad for some reason. — Nikola Markovinović, Aug 02 '12 at 14:55
Could you please [check sqlite datetime comparison post on SO?](http://stackoverflow.com/questions/1975737/sqlite-datetime-comparison) — Nikola Markovinović, Aug 02 '12 at 15:00
@NikolaMarkovinović, I have tested that too, Nikola. It does return with dataset if I don't have date in the where clause. — ThN, Aug 02 '12 at 15:01
@NikolaMarkovinović, I just found my problem when you mentioned format. Actually, my AlarmTime is of TEXT format instead of DateTime. I did it that way, because it is easier to dump the dataset to datagridview table like datagridview1.datasource := datatable1; — ThN, Aug 02 '12 at 15:29

score 0 · Answer 1 · answered Aug 02 '12 at 14:23

I don't have an instance spun up but this should work.

SELECT * FROM Btable WHERE Bday >= julianday(date('now')));

This should return a String like so:

2012-08-02 00:00:00

The thing that is wrong with the query is that it may be missing milliseconds or some other precision which would always evaluate to false.

score 0 · Accepted Answer · answered Aug 06 '12 at 13:57

Here is my answer.

Since my column ALARMTIME is TEXT, I am able to query my table with the following SQL statement and retrieve dataset I am looking for.

SELECT *
FROM Alarms
WHERE ALARMSTATE IN (0,1,2)
  AND ALARMPRIORITY IN (0,1,2,3,4)
  AND ALARMGROUP IN (0,1,2,3,4,5,6,7)
  AND ALARMTIME LIKE "2012/08/01%"
  ORDER BY ALARMTIME DESC

I simply can't convert my ALARMTIME text into datetime type.

What is the Sqlite SQL statement to find todays records only?

2 Answers2