CODEWITHSUNDEEP
sqldatabasesqlitedatetimeselect
user2531590
user2531590

Reputation:

SQLite Convert String to Date in SQL Statement

I am having some problem when trying to write an SQLite statement to get the sum of certain column with where clause by today's date:

SELECT SUM(amount) AS total, (substr(date, 7, 4) || '-' || substr(date, 4, 2) || '-' || substr(date, 1, 2)) AS Date FROM transactionRec 
WHERE type = 'W' AND Date BETWEEN DATE('now') AND DATE('now', '+1 day')

I am trying to convert my date column to correct date format before making the comparison. My mock up data as these:

enter image description here

By right, it should return me the record of last row since it's today's date but somehow, I am getting empty result using the SQL statement above.

Any ideas? Thanks in advance.

Upvotes: 0

Views: 8389

Answers (1)

PM 77-1
PM 77-1

Reputation: 13334

Two changes:

  1. I swapped month and day in the concatenation order
  2. Instead of alias Date I used SomeDate.


SELECT SUM(amount) AS total, 
       (substr(date, 7, 4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2)) AS SomeDate 
FROM transactionRec 
WHERE type = 'W' AND SomeDate BETWEEN DATE('now') AND DATE('now', '+1 day')

Apparently SQLite does not have a problem distinguishing between date field and Date() function, but gets confused when faced with Date column alias.

Upvotes: 2

Related Questions