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;

Answer 4

Well, there are a couple of ways to do it.

1. You could fill a temp table, using a loop and INSERT statements, and then select the contents of the table. You could create a table-valued UDF to do this, in fact.
2. You could also create 30 SELECT statements, and UNION them all together. But, frankly, I think you're better off with option 1.

ETA: Thinking about it, if all you want is the number of each day of the week in the past 30 days, you can probably do that just with some math, without returning 30 records.

There are 4 instances of each day of the week in any 30 day period, plus 2 extra days. So all you really need is to know what day of the week the first day in your period is, and the second day. Those days of the week have 5 instances.

Answer 5

I'm pretty lazy and just load a temp table and then do a group by select on that temp table

DECLARE @tmpDates TABLE (calDate DATETIME)
DECLARE @beginDate DATETIME
SET @beginDate = DATEADD(day,-30,GETDATE())
WHILE @beginDate < GETDATE()
BEGIN
    INSERT INTO @tmpDates ([calDate]) VALUES (@beginDate)
    SET @beginDate = DATEADD(DAY,1,@beginDate)
END


SELECT DATEPART(dw,[calDate]) AS [weekDay], COUNT(1) AS [dayCount]
FROM @tmpDates
GROUP BY DATEPART(dw,[calDate])

Answer 6

Number of times each day of the week got hit in the last 30 days:

SELECT DATENAME(dw,GETDATE())+' 5 times' as results
UNION ALL
SELECT DATENAME(dw,DATEADD(day,-1,GETDATE()))+' 5 times'
UNION ALL
SELECT DATENAME(dw,DATEADD(day,-2,GETDATE()))+' 4 times'
UNION ALL
SELECT DATENAME(dw,DATEADD(day,-3,GETDATE()))+' 4 times'
UNION ALL
SELECT DATENAME(dw,DATEADD(day,-4,GETDATE()))+' 4 times'
UNION ALL
SELECT DATENAME(dw,DATEADD(day,-5,GETDATE()))+' 4 times'
UNION ALL
SELECT DATENAME(dw,DATEADD(day,-6,GETDATE()))+' 4 times'

This really is about dividing 30 by 7

---

This gives me

results
Thursday 5 times
Wednesday 5 times
Tuesday 4 times
Monday 4 times
Sunday 4 times
Saturday 4 times
Friday 4 times