SQL Server So far cycle for last day of the month

Question

I want the last day of every month, so I wrote a query like

declare @date datetime set @date='31-jan-2012' while @date<='31-Dec-2012' Begin print @date set @date= DATEADD(M,1,@date) End 

But I got a result like

 Jan 31 2012 12:00AM Feb 29 2012 12:00AM Mar 29 2012 12:00AM Apr 29 2012 12:00AM May 29 2012 12:00AM Jun 29 2012 12:00AM Jul 29 2012 12:00AM Aug 29 2012 12:00AM Sep 29 2012 12:00AM Oct 29 2012 12:00AM Nov 29 2012 12:00AM Dec 29 2012 12:00AM 

Please help me

Answer 1

After all that everyone has said, here is the update:

 Declare @FromDate DateTime ; Select @FromDate = Convert(DateTime, '2012-1-1') ; With CTE ( Level , FirstDayOfMonth , Value ) As ( Select 1 As Level , @FromDate , DateAdd(Day, -1, DateAdd(Month, 1, @FromDate)) Union All Select Level + 1 , FirstDayOfMonth , DateAdd(Day, -1, DateAdd(Month, Level + 1, FirstDayOfMonth)) From CTE Where Level < 12 ) Select Value From CTE Order By Value 

This is a CTE (Common Table Expression). The first value should be the first day of the second month, after adding -1 day it will be the last day of January. And of course, this will give you the result.

What I did is just use DateAdd (Day, -1, [First day of next month]) , it's easy!

Just for fun, so I started on the first day of February, and let the engine detect the last day of January !: D Maybe next year after another sandy cousin the storm the earth will change its course, and January is no longer 31 days! This way your code will work then ...

Thanks to everyone for making me update this answer.

Hope this helps.

Answer 2

You may be better off: -

 declare @date datetime; declare @lastDay datetime; -- Start on the first day of the next month -- This'll ensure that your month add doesn't throw -- weird results set @date='01-Feb-2012' while @date<='01-Jan-2013' Begin -- Get last day of previous month set @lastDay = DATEADD(DAY, -1, @date) -- print that print @lastDay -- increment @date to move one month into the future. set @date= DATEADD(month,1,@date) End 

Answer 3

You do not specify the version of SQL Server that you are using.

If you are on SQL Server 2012 , you can use the EOMONTH() function.

Answer 4

Or you could try:

 Declare @Month Integer Set @Month = 1 While @Month <= 12 Begin Print DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()) +@Month ,0)) Set @Month = @Month+1 End 

Answer 5

Using the following function, you can achieve this query: -

 DECLARE @StartDate DATETIME SET @StartDate = '2012-01-31' DECLARE @EndDate DATETIME SET @EndDate = '2012-12-31' SELECT DISTINCT dbo.F_TABLE_DATE.END_OF_MONTH_DATE FROM dbo.F_TABLE_DATE(@StartDate, @EndDate) 

Here is a date function that I found many years ago, and I still use today, it contains everything related to dates that you might want.

 create function [dbo].[F_TABLE_DATE] ( @FIRST_DATE datetime, @LAST_DATE datetime ) returns @DATE table ( [DATE_ID] [int] not null primary key clustered, [DATE] [datetime] not null , [NEXT_DAY_DATE] [datetime] not null , [YEAR] [smallint] not null , [YEAR_QUARTER] [int] not null , [YEAR_MONTH] [int] not null , [YEAR_DAY_OF_YEAR] [int] not null , [QUARTER] [tinyint] not null , [MONTH] [tinyint] not null , [DAY_OF_YEAR] [smallint] not null , [DAY_OF_MONTH] [smallint] not null , [DAY_OF_WEEK] [tinyint] not null , [YEAR_NAME] [varchar] (4) not null , [YEAR_QUARTER_NAME] [varchar] (7) not null , [YEAR_MONTH_NAME] [varchar] (8) not null , [YEAR_MONTH_NAME_LONG] [varchar] (14) not null , [QUARTER_NAME] [varchar] (2) not null , [MONTH_NAME] [varchar] (3) not null , [MONTH_NAME_LONG] [varchar] (9) not null , [WEEKDAY_NAME] [varchar] (3) not null , [WEEKDAY_NAME_LONG] [varchar] (9) not null , [START_OF_YEAR_DATE] [datetime] not null , [END_OF_YEAR_DATE] [datetime] not null , [START_OF_QUARTER_DATE] [datetime] not null , [END_OF_QUARTER_DATE] [datetime] not null , [START_OF_MONTH_DATE] [datetime] not null , [END_OF_MONTH_DATE] [datetime] not null , [START_OF_WEEK_STARTING_SUN_DATE] [datetime] not null , [END_OF_WEEK_STARTING_SUN_DATE] [datetime] not null , [START_OF_WEEK_STARTING_MON_DATE] [datetime] not null , [END_OF_WEEK_STARTING_MON_DATE] [datetime] not null , [START_OF_WEEK_STARTING_TUE_DATE] [datetime] not null , [END_OF_WEEK_STARTING_TUE_DATE] [datetime] not null , [START_OF_WEEK_STARTING_WED_DATE] [datetime] not null , [END_OF_WEEK_STARTING_WED_DATE] [datetime] not null , [START_OF_WEEK_STARTING_THU_DATE] [datetime] not null , [END_OF_WEEK_STARTING_THU_DATE] [datetime] not null , [START_OF_WEEK_STARTING_FRI_DATE] [datetime] not null , [END_OF_WEEK_STARTING_FRI_DATE] [datetime] not null , [START_OF_WEEK_STARTING_SAT_DATE] [datetime] not null , [END_OF_WEEK_STARTING_SAT_DATE] [datetime] not null , [QUARTER_SEQ_NO] [int] not null , [MONTH_SEQ_NO] [int] not null , [WEEK_STARTING_SUN_SEQ_NO] [int] not null , [WEEK_STARTING_MON_SEQ_NO] [int] not null , [WEEK_STARTING_TUE_SEQ_NO] [int] not null , [WEEK_STARTING_WED_SEQ_NO] [int] not null , [WEEK_STARTING_THU_SEQ_NO] [int] not null , [WEEK_STARTING_FRI_SEQ_NO] [int] not null , [WEEK_STARTING_SAT_SEQ_NO] [int] not null , [JULIAN_DATE] [int] not null , [MODIFIED_JULIAN_DATE] [int] not null , [ISO_DATE] [varchar](10) not null , [ISO_YEAR_WEEK_NO] [int] not null , [ISO_WEEK_NO] [smallint] not null , [ISO_DAY_OF_WEEK] [tinyint] not null , [ISO_YEAR_WEEK_NAME] [varchar](8) not null , [ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] [varchar](10) not null , [DATE_FORMAT_YYYY_MM_DD] [varchar](10) not null , [DATE_FORMAT_YYYY_M_D] [varchar](10) not null , [DATE_FORMAT_MM_DD_YYYY] [varchar](10) not null , [DATE_FORMAT_M_D_YYYY] [varchar](10) not null , [DATE_FORMAT_MMM_D_YYYY] [varchar](12) not null , [DATE_FORMAT_MMMMMMMMM_D_YYYY] [varchar](18) not null , [DATE_FORMAT_MM_DD_YY] [varchar](8) not null , [DATE_FORMAT_M_D_YY] [varchar](8) not null ) as begin declare @cr varchar(2) select @cr = char(13)+Char(10) declare @ErrorMessage varchar(400) declare @START_DATE datetime declare @END_DATE datetime declare @LOW_DATE datetime declare @start_no int declare @end_no int -- Verify @FIRST_DATE is not null if @FIRST_DATE is null begin select @ErrorMessage = '@FIRST_DATE cannot be null' goto Error_Exit end -- Verify @LAST_DATE is not null if @LAST_DATE is null begin select @ErrorMessage = '@LAST_DATE cannot be null' goto Error_Exit end -- Verify @FIRST_DATE is not before 1754-01-01 IF @FIRST_DATE < '17540101' begin select @ErrorMessage = '@FIRST_DATE cannot before 1754-01-01'+ ', @FIRST_DATE = '+ isnull(convert(varchar(40),@FIRST_DATE,121),'NULL') goto Error_Exit end -- Verify @LAST_DATE is not after 9997-12-31 IF @LAST_DATE > '99971231' begin select @ErrorMessage = '@LAST_DATE cannot be after 9997-12-31'+ ', @LAST_DATE = '+ isnull(convert(varchar(40),@LAST_DATE,121),'NULL') goto Error_Exit end -- Verify @FIRST_DATE is not after @LAST_DATE if @FIRST_DATE > @LAST_DATE begin select @ErrorMessage = '@FIRST_DATE cannot be after @LAST_DATE'+ ', @FIRST_DATE = '+ isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')+ ', @LAST_DATE = '+ isnull(convert(varchar(40),@LAST_DATE,121),'NULL') goto Error_Exit end -- Set @START_DATE = @FIRST_DATE at midnight select @START_DATE = dateadd(dd,datediff(dd,0,@FIRST_DATE),0) -- Set @END_DATE = @LAST_DATE at midnight select @END_DATE = dateadd(dd,datediff(dd,0,@LAST_DATE),0) -- Set @LOW_DATE = earliest possible SQL Server datetime select @LOW_DATE = convert(datetime,'17530101') -- Find the number of day from 1753-01-01 to @START_DATE and @END_DATE select @start_no = datediff(dd,@LOW_DATE,@START_DATE) , @end_no = datediff(dd,@LOW_DATE,@END_DATE) -- Declare number tables declare @num1 table (NUMBER int not null primary key clustered) declare @num2 table (NUMBER int not null primary key clustered) declare @num3 table (NUMBER int not null primary key clustered) -- Declare table of ISO Week ranges declare @ISO_WEEK table ( [ISO_WEEK_YEAR] int not null primary key clustered, [ISO_WEEK_YEAR_START_DATE] datetime not null, [ISO_WEEK_YEAR_END_DATE] Datetime not null ) -- Find rows needed in number tables declare @rows_needed int declare @rows_needed_root int select @rows_needed = @end_no - @start_no + 1 select @rows_needed = case when @rows_needed < 10 then 10 else @rows_needed end select @rows_needed_root = convert(int,ceiling(sqrt(@rows_needed))) -- Load number 0 to 16 insert into @num1 (NUMBER) select NUMBER = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 order by 1 -- Load table with numbers zero thru square root of the number of rows needed +1 insert into @num2 (NUMBER) select NUMBER = a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER) from @num1 a cross join @num1 b cross join @num1 c where a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER) < @rows_needed_root order by 1 -- Load table with the number of rows needed for the date range insert into @num3 (NUMBER) select NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER) from @num2 a cross join @num2 b where a.NUMBER+(@rows_needed_root*b.NUMBER) < @rows_needed order by 1 declare @iso_start_year int declare @iso_end_year int select @iso_start_year = datepart(year,dateadd(year,-1,@start_date)) select @iso_end_year = datepart(year,dateadd(year,1,@end_date)) -- Load table with start and end dates for ISO week years insert into @ISO_WEEK ( [ISO_WEEK_YEAR], [ISO_WEEK_YEAR_START_DATE], [ISO_WEEK_YEAR_END_DATE] ) select [ISO_WEEK_YEAR] = a.NUMBER, [0ISO_WEEK_YEAR_START_DATE] = dateadd(dd,(datediff(dd,@LOW_DATE, dateadd(day,3,dateadd(year,a.[NUMBER]-1900,0)) )/7)*7,@LOW_DATE), [ISO_WEEK_YEAR_END_DATE] = dateadd(dd,-1,dateadd(dd,(datediff(dd,@LOW_DATE, dateadd(day,3,dateadd(year,a.[NUMBER]+1-1900,0)) )/7)*7,@LOW_DATE)) from ( select NUMBER = NUMBER+@iso _start_year from @num3 where NUMBER+@iso _start_year <= @iso_end_year ) a order by a.NUMBER -- Load Date table insert into @DATE select [DATE_ID] = a.[DATE_ID] , [DATE] = a.[DATE] , [NEXT_DAY_DATE] = dateadd(day,1,a.[DATE]) , [YEAR] = datepart(year,a.[DATE]) , [YEAR_QUARTER] = (10*datepart(year,a.[DATE]))+datepart(quarter,a.[DATE]) , [YEAR_MONTH] = (100*datepart(year,a.[DATE]))+datepart(month,a.[DATE]) , [YEAR_DAY_OF_YEAR] = (1000*datepart(year,a.[DATE]))+ datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 , [QUARTER] = datepart(quarter,a.[DATE]) , [MONTH] = datepart(month,a.[DATE]) , [DAY_OF_YEAR] = datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 , [DAY_OF_MONTH] = datepart(day,a.[DATE]) , [DAY_OF_WEEK] = -- Sunday = 1, Monday = 2, ,,,Saturday = 7 (datediff(dd,'17530107',a.[DATE])%7)+1 , [YEAR_NAME] = datename(year,a.[DATE]) , [YEAR_QUARTER_NAME] = datename(year,a.[DATE])+' Q'+datename(quarter,a.[DATE]) , [YEAR_MONTH_NAME] = datename(year,a.[DATE])+' '+left(datename(month,a.[DATE]),3) , [YEAR_MONTH_NAME_LONG] = datename(year,a.[DATE])+' '+datename(month,a.[DATE]) , [QUARTER_NAME] = 'Q'+datename(quarter,a.[DATE]) , [MONTH_NAME] = left(datename(month,a.[DATE]),3) , [MONTH_NAME_LONG] = datename(month,a.[DATE]) , [WEEKDAY_NAME] = left(datename(weekday,a.[DATE]),3) , [WEEKDAY_NAME_LONG] = datename(weekday,a.[DATE]), [START_OF_YEAR_DATE] = dateadd(year,datediff(year,0,a.[DATE]),0) , [END_OF_YEAR_DATE] = dateadd(day,-1,dateadd(year,datediff(year,0,a.[DATE])+1,0)) , [START_OF_QUARTER_DATE] = dateadd(quarter,datediff(quarter,0,a.[DATE]),0) , [END_OF_QUARTER_DATE] = dateadd(day,-1,dateadd(quarter,datediff(quarter,0,a.[DATE])+1,0)) , [START_OF_MONTH_DATE] = dateadd(month,datediff(month,0,a.[DATE]),0) , [END_OF_MONTH_DATE] = dateadd(day,-1,dateadd(month,datediff(month,0,a.[DATE])+1,0)), [START_OF_WEEK_STARTING_SUN_DATE] = dateadd(dd,(datediff(dd,'17530107',a.[DATE])/7)*7,'17530107'), [END_OF_WEEK_STARTING_SUN_DATE] = dateadd(dd,((datediff(dd,'17530107',a.[DATE])/7)*7)+6,'17530107'), [START_OF_WEEK_STARTING_MON_DATE] = dateadd(dd,(datediff(dd,'17530101',a.[DATE])/7)*7,'17530101'), [END_OF_WEEK_STARTING_MON_DATE] = dateadd(dd,((datediff(dd,'17530101',a.[DATE])/7)*7)+6,'17530101'), [START_OF_WEEK_STARTING_TUE_DATE] = dateadd(dd,(datediff(dd,'17530102',a.[DATE])/7)*7,'17530102'), [END_OF_WEEK_STARTING_TUE_DATE] = dateadd(dd,((datediff(dd,'17530102',a.[DATE])/7)*7)+6,'17530102'), [START_OF_WEEK_STARTING_WED_DATE] = dateadd(dd,(datediff(dd,'17530103',a.[DATE])/7)*7,'17530103'), [END_OF_WEEK_STARTING_WED_DATE] = dateadd(dd,((datediff(dd,'17530103',a.[DATE])/7)*7)+6,'17530103'), [START_OF_WEEK_STARTING_THU_DATE] = dateadd(dd,(datediff(dd,'17530104',a.[DATE])/7)*7,'17530104'), [END_OF_WEEK_STARTING_THU_DATE] = dateadd(dd,((datediff(dd,'17530104',a.[DATE])/7)*7)+6,'17530104'), [START_OF_WEEK_STARTING_FRI_DATE] = dateadd(dd,(datediff(dd,'17530105',a.[DATE])/7)*7,'17530105'), [END_OF_WEEK_STARTING_FRI_DATE] = dateadd(dd,((datediff(dd,'17530105',a.[DATE])/7)*7)+6,'17530105'), [START_OF_WEEK_STARTING_SAT_DATE] = dateadd(dd,(datediff(dd,'17530106',a.[DATE])/7)*7,'17530106'), [END_OF_WEEK_STARTING_SAT_DATE] = dateadd(dd,((datediff(dd,'17530106',a.[DATE])/7)*7)+6,'17530106'), [QUARTER_SEQ_NO] = datediff(quarter,@LOW_DATE,a.[DATE]), [MONTH_SEQ_NO] = datediff(month,@LOW_DATE,a.[DATE]), [WEEK_STARTING_SUN_SEQ_NO] = datediff(day,'17530107',a.[DATE])/7, [WEEK_STARTING_MON_SEQ_NO] = datediff(day,'17530101',a.[DATE])/7, [WEEK_STARTING_TUE_SEQ_NO] = datediff(day,'17530102',a.[DATE])/7, [WEEK_STARTING_WED_SEQ_NO] = datediff(day,'17530103',a.[DATE])/7, [WEEK_STARTING_THU_SEQ_NO] = datediff(day,'17530104',a.[DATE])/7, [WEEK_STARTING_FRI_SEQ_NO] = datediff(day,'17530105',a.[DATE])/7, [WEEK_STARTING_SAT_SEQ_NO] = datediff(day,'17530106',a.[DATE])/7, [JULIAN_DATE] = datediff(day,@LOW_DATE,a.[DATE])+2361331, [MODIFIED_JULIAN_DATE] = datediff(day,'18581117',a.[DATE]), --/* [ISO_DATE] = replace(convert(char(10),a.[DATE],111),'/','-') , [ISO_YEAR_WEEK_NO] = (100*b.[ISO_WEEK_YEAR])+ (datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 , [ISO_WEEK_NO] = (datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 , [ISO_DAY_OF_WEEK] = -- Sunday = 1, Monday = 2, ,,,Saturday = 7 (datediff(dd,@LOW_DATE,a.[DATE])%7)+1 , [ISO_YEAR_WEEK_NAME] = convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+ right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) , [ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] = convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+ right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) + '-'+convert(varchar(1),(datediff(dd,@LOW_DATE,a.[DATE])%7)+1) , --*/ [DATE_FORMAT_YYYY_MM_DD] = convert(char(10),a.[DATE],111) , [DATE_FORMAT_YYYY_M_D] = convert(varchar(10), convert(varchar(4),year(a.[DATE]))+'/'+ convert(varchar(2),day(a.[DATE]))+'/'+ convert(varchar(2),month(a.[DATE]))), [DATE_FORMAT_MM_DD_YYYY] = convert(char(10),a.[DATE],101) , [DATE_FORMAT_M_D_YYYY] = convert(varchar(10), convert(varchar(2),month(a.[DATE]))+'/'+ convert(varchar(2),day(a.[DATE]))+'/'+ convert(varchar(4),year(a.[DATE]))), [DATE_FORMAT_MMM_D_YYYY] = convert(varchar(12), left(datename(month,a.[DATE]),3)+' '+ convert(varchar(2),day(a.[DATE]))+', '+ convert(varchar(4),year(a.[DATE]))), [DATE_FORMAT_MMMMMMMMM_D_YYYY] = convert(varchar(18), datename(month,a.[DATE])+' '+ convert(varchar(2),day(a.[DATE]))+', '+ convert(varchar(4),year(a.[DATE]))), [DATE_FORMAT_MM_DD_YY] = convert(char(8),a.[DATE],1) , [DATE_FORMAT_M_D_YY] = convert(varchar(8), convert(varchar(2),month(a.[DATE]))+'/'+ convert(varchar(2),day(a.[DATE]))+'/'+ right(convert(varchar(4),year(a.[DATE])),2)) from ( -- Derived table is all dates needed for date range select top 100 percent [DATE_ID] = aa.[NUMBER], [DATE] = dateadd(dd,aa.[NUMBER],@LOW_DATE) from ( select NUMBER = NUMBER+@start _no from @num3 where NUMBER+@start _no <= @end_no ) aa order by aa.[NUMBER] ) a join -- Match each date to the proper ISO week year @ISO_WEEK b on a.[DATE] between b.[ISO_WEEK_YEAR_START_DATE] and b.[ISO_WEEK_YEAR_END_DATE] order by a.[DATE_ID] return Error_Exit: -- Return a pesudo error message by trying to -- convert an error message string to an int. -- This method is used because the error displays -- the string it was trying to convert, and so the -- calling application sees a formatted error message. declare @error int set @error = convert(int,@ cr+@cr + '*******************************************************************' +@cr + '* Error in function F_TABLE_DATE:' +@cr +'* '+ isnull(@ErrorMessage,'Unknown Error') +@cr + '*******************************************************************' +@cr +@cr ) return end GO 

Answer 6

Try:

 with cte as (select convert(date, '2012-01-31', 102) lastdate union all select dateadd(d,-1,dateadd(m,1,(dateadd(d,1,lastdate)))) from cte where lastdate < '2012-12-31') select * from cte 

Answer 7

If you have a Numbers (*) table, this is simple:

 select DATEADD(month,n,'20120131') from Numbers where n between 0 and all 

Basically, DATEADD(month,... will automatically close the daily part of its result until the last day of the month if necessary. Thus, if you always add months to a date that is on the 31st, this will lead to the desired result.

---

(*) if you do not, just add a CTE before the request:

 ;with Numbers (n) as ( select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 ) select DATEADD(month,n,'20120131') from Numbers