Select last 30 days with a sql query

Question

I am looking for the number of Mon,Tues, Wed, Thur, Fri, Sat, Sun in the past 30 days. Can I select the last 30 days date and day of week without an actual database table? Something like

SELECT --everything between
convert(date,GETDATE()), DATENAME(DW, GETDATE())
--and
convert(date,GETDATE() - 30), DATENAME(DW, GETDATE())

Answer 1

You can use a recursive CTE:

;WITH CTE AS
(
    SELECT convert(date,GETDATE()) sDate, DATENAME(DW, GETDATE()) sDayofWeek
    UNION ALL
    SELECT DATEADD(DAY,-1,sDate), DATENAME(DW, DATEADD(DAY,-1,sDate))
    FROM CTE
    WHERE sDate > GETDATE()-29
)
SELECT * FROM CTE

Answer 2

WITH cteCount AS (
    SELECT DATENAME(dw, GETDATE()) dw, 1 ix 
    UNION ALL 
    SELECT DATENAME(dw, DATEADD(d, -ix, GETDATE())), ix+1 FROM cteCount WHERE ix<30
)
SELECT dw, COUNT(1) cnt FROM cteCount GROUP BY dw

Answer 3

A couple solutions:

SELECT ... From ... WHERE date > DATEADD(year, -1, GETDATE())

Also, I think this statement will work with MySQL:

select date_sub(now(),interval 30 day)as Datebefore30days;