select last month's data when the date column is a string

Question

I finding the sum of revenue last month. Currently, I'm filtering by giving where condition. For example if I need last month's data given the current date is '20191111'. My query to get October's data is

Select sum(revenue)
from Table 1
where date between '20191001' and '20191031'

Note : My date column is a string. How can I modify it to fetch the last month's record automatically?

Possible duplicate of [how to convert a string to date in mysql?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) — richyen, Nov 22 '19 at 19:30
How can I convert and use the converted date to get the last month's records — user62367, Nov 22 '19 at 19:32
The best way to accomplish this is **fixing** the column (because the schema really is _broken_ right now) to use a date/datetime type instead of a string type. — Joel Coehoorn, Nov 22 '19 at 19:51

score 0 · Answer 1 · answered Nov 22 '19 at 21:29

You're nearly there, although if your date column in the table is currently in String format, I would consider casting that column as a date data type, then using greater than and less than comparisons instead of the between clause.

Try something like the following:

SELECT SUM(revenue)
FROM Table 1
WHERE CAST(date AS DATE) >= '20191001' AND CAST(date AS DATE) < '20191101'

To fetch the last month's revenue automatically, or the total revenue for the month as of the current date, you could use something like the following:

Get the last months revenue:

SELECT SUM(revenue)
FROM Table 1
WHERE MONTH(CAST(date AS DATE)) < (MONTH(GETDATE()) - 1) 
AND MONTH(CAST(date AS DATE)) > (MONTH(GETDATE()) - 2)

select last month's data when the date column is a string

1 Answers1