MS SQL Tips & Tricks

How to get a table with list of days of current month

The simpliest way to get the table of days for w whole month containing specified date is: 

declare @cDate datetime;
set @cDate=GetDate();

select dateadd(m,datediff(m, 0,@cDate),0)+
	   pm1.Digit+pm2.Digit*10 as Date
from(
	select cast(1 as int) as Digit
	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 0
	) as pm1
inner join (
		select cast(0 as int) as Digit
			union select 1
			union select 2
			union select 3
	)  as pm2 on (1=1)
where datepart(m, @cDate)=
	datepart(m,
			dateadd(m,datediff(m, 0,@cDate),0)+
			pm1.Digit+pm2.Digit*10)

See also


Learn more tricks

Add Comment

Comments