Home > Back-end >  How to query all data with current date when column is datetime in sqlite?
How to query all data with current date when column is datetime in sqlite?

Time:04-13

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?

CodePudding user response:

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
  • Related