Search code examples
sqlitedatedatetimewhere-clausedate-arithmetic

How to query all data with current date when column is datetime in sqlite?

I have created table with field createdDate as DATETIME. Now I am trying to get all the data with same date regardless of the time

For example createdDate values are

2022-02-02 10:00:00
2022-03-02 12:00:00
2022-04-10 12:00:00
2022-04-12 12:00:00
2022-04-12 13:00:00

Lets say today is April 12 and I want all data that has been created today, this is my query statement

SELECT * from sample WHERE createdDate = date('now')

I need to get all those with 2022-04-12 dates but I'm getting none. Is this possible or I have no choice but to use DATE as field type?

Solution

  • You should strip off the time part of your timestamps with the function date():

    WHERE date(createdDate) = date('now')

    or:

    WHERE date(createdDate) = CURRENT_DATE