select a.DT, LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1), LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107'), DwainsWay = 1+dateadd(mm,datediff(mm,-1,a.DT),-1)-DATEPART(weekday, dateadd(mm,datediff(mm,-1,a.DT),-1))from ( -- Test data select DT = getdate() union all select DT = dateadd(mm,-2,getdate()) union all select DT = dateadd(mm,-1,getdate()) union all select DT = dateadd(mm,1,getdate()) union all select DT = dateadd(mm,2,getdate()) union all select DT = dateadd(mm,3,getdate()) union all select DT = dateadd(mm,4,getdate()) union all select DT = dateadd(mm,5,getdate()) union all select DT = dateadd(mm,6,getdate()) union all select DT = dateadd(mm,7,getdate()) union all select DT = dateadd(mm,8,getdate()) union all select DT = dateadd(mm,9,getdate()) union all select DT = dateadd(mm,10,getdate()) union all select DT = dateadd(mm,11,getdate()) ) aorder by a.DT

set datefirst 4

set language 'spanish'

select a.DT ,LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1) ,LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107') ,LastSundayUsingCalendarFunction=c.LastSundayfrom ( -- Test data select DT = getdate() union all select DT = dateadd(mm,-2,getdate()) union all select DT = dateadd(mm,-1,getdate()) union all select DT = dateadd(mm,1,getdate()) union all select DT = dateadd(mm,2,getdate()) union all select DT = dateadd(mm,3,getdate()) union all select DT = dateadd(mm,4,getdate()) union all select DT = dateadd(mm,5,getdate()) union all select DT = dateadd(mm,6,getdate()) union all select DT = dateadd(mm,7,getdate()) union all select DT = dateadd(mm,8,getdate()) union all select DT = dateadd(mm,9,getdate()) union all select DT = dateadd(mm,10,getdate()) union all select DT = dateadd(mm,11,getdate()) UNION ALL SELECT '1753-01-07' ) aCROSS APPLY dbo.GenerateCalendar(a.Dt, 1) bCROSS APPLY ( SELECT LastSunday=c.[Date] FROM dbo.GenerateCalendar(b.LDtOfMo, -7) c -- Change 'SU' as appropriate to your language setting WHERE [Last] = 1 AND WkDName2 = 'SU') corder by a.DT

CREATE FUNCTION [dbo].[GenerateCalendar] ( @FromDate DATETIME, @NoDays INT )RETURNS TABLE WITH SCHEMABINDING AS RETURN--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen) WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16) SELECT [SeqNo] = t.N, [Date] = dt.DT, [Year] = dp.YY, [YrNN] = dp.YY % 100, [YYYYMM] = dp.YY * 100 + dp.MM, [BuddhaYr] = dp.YY + 543, [Month] = dp.MM, [Day] = dp.DD, [WkDNo] = DATEPART(dw,dt.DT), [WkDName] = CONVERT(NCHAR(9),dp.DW), [WkDName2] = CONVERT(NCHAR(2),dp.DW), [WkDName3] = CONVERT(NCHAR(3),dp.DW), [JulDay] = dp.DY, [JulWk] = dp.DY/7+1, [WkNo] = dp.DD/7+1, [Qtr] = DATEPART(qq,dt.Dt), [Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1, [LdOfMo] = DATEPART(dd,dp.LDtOfMo), [LDtOfMo] = dp.LDtOfMo FROM cteTally t CROSS APPLY ( --=== Create the date SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate) ) dt CROSS APPLY ( --=== Create the other parts from the date above using a "cCA" -- (Cascading CROSS APPLY, Acourtesy of ChrisM) SELECT YY = DATEPART(yy,dt.DT), MM = DATEPART(mm,dt.DT), DD = DATEPART(dd,dt.DT), DW = DATENAME(dw,dt.DT), Dy = DATEPART(dy,dt.DT), LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1) ) dp

select a.DT, LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1), LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107')from ( -- Test data select DT = convert(smalldatetime,getdate()) union all select DT = convert(smalldatetime,dateadd(mm,-2,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,-1,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,1,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,2,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,3,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,4,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,5,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,6,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,7,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,8,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,9,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,10,getdate())) union all select DT = convert(smalldatetime,dateadd(mm,11,getdate())) ) aorder by a.DT

DT LastDayofMonth LastSundayofMonth----------------------- ----------------------- -----------------------2012-12-11 19:39:00 2012-12-31 00:00:00.000 2012-12-30 00:00:00.0002013-01-11 19:39:00 2013-01-31 00:00:00.000 2013-01-27 00:00:00.0002013-02-11 19:39:00 2013-02-28 00:00:00.000 2013-02-24 00:00:00.0002013-03-11 19:39:00 2013-03-31 00:00:00.000 2013-03-31 00:00:00.0002013-04-11 19:39:00 2013-04-30 00:00:00.000 2013-04-28 00:00:00.0002013-05-11 19:39:00 2013-05-31 00:00:00.000 2013-05-26 00:00:00.0002013-06-11 19:39:00 2013-06-30 00:00:00.000 2013-06-30 00:00:00.0002013-07-11 19:39:00 2013-07-31 00:00:00.000 2013-07-28 00:00:00.0002013-08-11 19:39:00 2013-08-31 00:00:00.000 2013-08-25 00:00:00.0002013-09-11 19:39:00 2013-09-30 00:00:00.000 2013-09-29 00:00:00.0002013-10-11 19:39:00 2013-10-31 00:00:00.000 2013-10-27 00:00:00.0002013-11-11 19:39:00 2013-11-30 00:00:00.000 2013-11-24 00:00:00.0002013-12-11 19:39:00 2013-12-31 00:00:00.000 2013-12-29 00:00:00.0002014-01-11 19:39:00 2014-01-31 00:00:00.000 2014-01-26 00:00:00.000

set language 'english'goset language 'spanish'goselect a.DT, LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1), LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107'), DwainsWay = 1+dateadd(mm,datediff(mm,-1,a.DT),-1)-DATEPART(weekday, dateadd(mm,datediff(mm,-1,a.DT),-1))from ( -- Test data select DT = getdate() union all select DT = dateadd(mm,-2,getdate()) union all select DT = dateadd(mm,-1,getdate()) union all select DT = dateadd(mm,1,getdate()) union all select DT = dateadd(mm,2,getdate()) union all select DT = dateadd(mm,3,getdate()) union all select DT = dateadd(mm,4,getdate()) union all select DT = dateadd(mm,5,getdate()) union all select DT = dateadd(mm,6,getdate()) union all select DT = dateadd(mm,7,getdate()) union all select DT = dateadd(mm,8,getdate()) union all select DT = dateadd(mm,9,getdate()) union all select DT = dateadd(mm,10,getdate()) union all select DT = dateadd(mm,11,getdate()) ) aorder by a.DT

Changed language setting to us_english.Se cambió la configuración de idioma a Español.DT LastDayofMonth LastSundayofMonth DwainsWay----------------------- ----------------------- ----------------------- -----------------------2012-12-11 19:49:06.253 2012-12-31 00:00:00.000 2012-12-30 00:00:00.000 2012-12-31 00:00:00.0002013-01-11 19:49:06.253 2013-01-31 00:00:00.000 2013-01-27 00:00:00.000 2013-01-28 00:00:00.0002013-02-11 19:49:06.253 2013-02-28 00:00:00.000 2013-02-24 00:00:00.000 2013-02-25 00:00:00.0002013-03-11 19:49:06.253 2013-03-31 00:00:00.000 2013-03-31 00:00:00.000 2013-03-25 00:00:00.0002013-04-11 19:49:06.253 2013-04-30 00:00:00.000 2013-04-28 00:00:00.000 2013-04-29 00:00:00.0002013-05-11 19:49:06.253 2013-05-31 00:00:00.000 2013-05-26 00:00:00.000 2013-05-27 00:00:00.0002013-06-11 19:49:06.253 2013-06-30 00:00:00.000 2013-06-30 00:00:00.000 2013-06-24 00:00:00.0002013-07-11 19:49:06.253 2013-07-31 00:00:00.000 2013-07-28 00:00:00.000 2013-07-29 00:00:00.0002013-08-11 19:49:06.253 2013-08-31 00:00:00.000 2013-08-25 00:00:00.000 2013-08-26 00:00:00.0002013-09-11 19:49:06.253 2013-09-30 00:00:00.000 2013-09-29 00:00:00.000 2013-09-30 00:00:00.0002013-10-11 19:49:06.253 2013-10-31 00:00:00.000 2013-10-27 00:00:00.000 2013-10-28 00:00:00.0002013-11-11 19:49:06.253 2013-11-30 00:00:00.000 2013-11-24 00:00:00.000 2013-11-25 00:00:00.0002013-12-11 19:49:06.253 2013-12-31 00:00:00.000 2013-12-29 00:00:00.000 2013-12-30 00:00:00.0002014-01-11 19:49:06.253 2014-01-31 00:00:00.000 2014-01-26 00:00:00.000 2014-01-27 00:00:00.000(14 row(s) affected)

declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect DATEDIFF(day, '175301017', @smalldatetime)

declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')

DECLARE @Year INTSET @Year =2012SELECT months,MAX(dates) AS DT_MONTHFROM (SELECT MONTH(DATEADD(DAY, number-1, DATEADD(YEAR, @Year-1900, 0))) AS MONTHS, DATEADD(DAY, number-1, DATEADD(YEAR, @Year-1900, 0)) AS DATES FROM MASTER..spt_values WHERE type='P' AND number BETWEEN 1 AND DATEDIFF(DAY, DATEADD(YEAR, @Year-1900, 0), DATEADD(YEAR, @Year-1900+1, 0))) AS TWHERE DATENAME(WEEKDAY, dates)='Sunday'GROUP BY months,DATEADD(MONTH, DATEDIFF(MONTH, 0, dates), 0)