Generate all days of last month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generate all days of last month

Author

Topic

cocofuc25
Starting Member

14 Posts

Posted - 2014-06-09 : 00:41:43

Hi guys,

what query should i use to generate a data of all days on last month, for example, if today is june so last month would be may.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-09 : 01:11:41

[code]; with dates as
(
select [date] = dateadd(month, datediff(month, 0, getdate()) -1, 0)
union all
select [date] = dateadd(day, 1, [date])
from dates
where [date] < dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))
)
select *
from dates[/code]

KH
[spoiler]Time is always against us[/spoiler]

cocofuc25
Starting Member

14 Posts

Posted - 2014-06-09 : 02:15:10

hi khtan

a dates is a coloumn inside of interfacetraffic, actualy the name of the coloumn is datetime, and i want to filter the data inside of datetime coloumn to be all dates of previous month, so for example, if today is june, so the query would give me a return of 1 of may 2014 until 31 of may 2014

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-09 : 04:44:09

Hi bram,
Try this


DECLARE @Date date = '06/08/2014'
				;WITH Calender
			AS (   SELECT   DATEADD(dd,-DAY(DATEADD(MONTH,-1,@Date))+1,DATEADD(MONTH,-1,@Date)) AS [Date]
		           UNION    ALL
		           SELECT   DATEADD(DD,1,[Date]) FROM  Calender 
		           WHERE    DATEADD(DD,1,[Date]) <= DATEADD(dd,-(DAY(@Date)),@Date)
		)
		 SELECT * FROM  Calender

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources