Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

Every Date in a Month - SQL Server

RSS
Modified on Mon, Aug 01, 2011, 6:23 PM by Administrator 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-2018, Patrick Jasinski.