SQLite Convert String to Date in SQL Statement

Question

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.

Since your Date column is TEXT try DATE(Date) BETWEEN ... — PM 77-1
– PM 77-1, Commented Sep 6, 2014 at 3:55
OK. One more attempt. strftime('%d/%m/%Y', Date) BETWEEN ... — PM 77-1
– PM 77-1, Commented Sep 6, 2014 at 4:03
@PM77-1 I edited my SQL statement as: 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 strftime('%d/%m/%Y', Date) BETWEEN DATE(Date) AND DATE('now', '+1 day') but still does not work — user2531590
– user2531590, Commented Sep 6, 2014 at 4:22
No. AND strftime('%d/%m/%Y', Date) BETWEEN DATE('now') AND DATE('now', '+1 day') If you paste your sample data as text (not image) I would be able to create a demo. — PM 77-1
– PM 77-1, Commented Sep 6, 2014 at 4:24

PM 77-1 · Accepted Answer · 2014-09-06 05:59:12Z

2

Two changes:

I swapped month and day in the concatenation order
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.

edited Sep 6, 2014 at 5:59

answered Sep 6, 2014 at 5:53

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2531590 Over a year ago

But do you have any idea how to get previous week, month and year?

Collectives™ on Stack Overflow

SQLite Convert String to Date in SQL Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related