### Navigation

Quick Search
»
Advanced Search »

### Contributor Links

 Create a new Page Administration File Management Login/Logout Your Profile

# Every Date in a Month - SQL Server

Modified on Mon, Aug 01, 2011, 6:23 PM Categorized as SQL Server

## Overview¶

Given a specific date, we want a single record for each date in the calendar month containing the date.

## Solution¶

{copytext|div1}
declare
@input datetime
,@FirstDayThisMonth datetime
,@LastDayThisMonth datetime

select
@input = '1/12/2009'
,@FirstDayThisMonth  = dateadd(day, 1-day(@input), @input)
,@LastDayThisMonth   = dateadd(day, 0-day(@input), dateadd(month, 1, @input))
;
with a as (
select 0 as n
union select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
union select 11
union select 12
union select 13
union select 14
union select 15
union select 16
union select 17
union select 18
union select 19
union select 20
union select 21
union select 22
union select 23
union select 24
union select 25
union select 26
union select 27
union select 28
union select 29
union select 30
union select 31
)
,b as (
select
result = dateadd(d, a.n, @FirstDayThisMonth)
from
a
)
select
result
from
b
where
result between @FirstDayThisMonth and @LastDayThisMonth

### Solution #2¶

DECLARE @DateFrom DATE = DATEADD(day, -30, GETDATE())

DECLARE @DateTo DATE
SET @DateTo = GETDATE()

;WITH DateRanges AS(
SELECT DateValue = @DateFrom
UNION ALL
SELECT DATEADD(DAY, 1, DateValue)
FROM DateRanges
WHERE DateValue < @DateTo
)
select * from DateRanges

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2019, Patrick Jasinski.