CODEWITHSUNDEEP
androidsqlsqlite
sammyukavi
sammyukavi

Reputation: 1511

SQLite not selecting date

My SQlite database in my app stores the date in the format of yyyy-MM-dd HH:mm:ss. I want to query a transaction that happened on a certain month of a year but my code is not working :

SELECT COUNT(id_sale) AS total_transactions,sold_to, 
strftime('%m', sale_date) AS month, 
strftime('%Y', sale_date) AS year,
sale_date FROM sales  WHERE  month=5 AND year=2015

Upvotes: 1

Views: 78

Answers (3)

sammyukavi
sammyukavi

Reputation: 1511

After some research I discovered that for some reason I had to do some casting for it to work.

    SELECT COUNT(id_sale) AS total_transactions,sold_to,
CAST(strftime('%m', sale_date) AS INTEGER) month, 
    CAST(strftime('%Y', sale_date) AS INTEGER) year,
    sale_date FROM sales  WHERE  month=5

Upvotes: 2

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

I would suggest a simple string matching:

... WHERE sale_date LIKE "2015-05-%"

I omitted result columns from query as I do not understand what is your intent, mixing aggregate function without GROUP BY and duplicating data.

Upvotes: 0

sqlab
sqlab

Reputation: 6436

As your date values are strings, you need no casting. You can compare directly with string values in your first select statement

SELECT 
   COUNT(id_sale) AS total_transactions,
   sold_to, 
   strftime('%m', sale_date) AS month, 
   strftime('%Y', sale_date) AS year,
   sale_date 
FROM sales  
   WHERE  month='05' AND year='2015'

Upvotes: 0

Related Questions