Last Sunday of a month in sql

 Author Message var05 SSC-Enthusiastic Group: General Forum Members Points: 183 Visits: 437 Hi all,I would like to know how to find last sunday of a month in sql... I wanted basically to find last sunday of month between feb and august in sql...Any help on this?Thanks Michael Valentine Jones SSCertifiable Group: General Forum Members Points: 5774 Visits: 11771 Find the last day of the month, and then find the Sunday on or before that date.`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 = 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`Results:`DT LastDayofMonth LastSundayofMonth----------------------- ----------------------- -----------------------2012-12-09 18:30:40.447 2012-12-31 00:00:00.000 2012-12-30 00:00:00.0002013-01-09 18:30:40.447 2013-01-31 00:00:00.000 2013-01-27 00:00:00.0002013-02-09 18:30:40.447 2013-02-28 00:00:00.000 2013-02-24 00:00:00.0002013-03-09 18:30:40.447 2013-03-31 00:00:00.000 2013-03-31 00:00:00.0002013-04-09 18:30:40.447 2013-04-30 00:00:00.000 2013-04-28 00:00:00.0002013-05-09 18:30:40.447 2013-05-31 00:00:00.000 2013-05-26 00:00:00.0002013-06-09 18:30:40.447 2013-06-30 00:00:00.000 2013-06-30 00:00:00.0002013-07-09 18:30:40.447 2013-07-31 00:00:00.000 2013-07-28 00:00:00.0002013-08-09 18:30:40.447 2013-08-31 00:00:00.000 2013-08-25 00:00:00.0002013-09-09 18:30:40.447 2013-09-30 00:00:00.000 2013-09-29 00:00:00.0002013-10-09 18:30:40.447 2013-10-31 00:00:00.000 2013-10-27 00:00:00.0002013-11-09 18:30:40.447 2013-11-30 00:00:00.000 2013-11-24 00:00:00.0002013-12-09 18:30:40.447 2013-12-31 00:00:00.000 2013-12-29 00:00:00.0002014-01-09 18:30:40.447 2014-01-31 00:00:00.000 2014-01-26 00:00:00.000`Start of Week Function:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307 dwain.c SSCertifiable Group: General Forum Members Points: 7335 Visits: 6431 If you don't like magic numbers, I think you can do it this way too:`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` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables var05 SSC-Enthusiastic Group: General Forum Members Points: 183 Visits: 437 Thanks all for your reply....Shall try and let you know guys! Michael Valentine Jones SSCertifiable Group: General Forum Members Points: 5774 Visits: 11771 dwain.c (2/11/2013)If you don't like magic numbers, I think you can do it this way too:`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`Your code, "DwainsWay", is sensitive to the setting of DATEFIRST and to the setting for language.You can see what happens if you put either of these before your code.I don't see how you code eliminates "magic numbers", since it uses the same -1 (Date 18991231) as my code.`set datefirst 4``set language 'spanish'` ScottPletcher SSCertifiable Group: General Forum Members Points: 7931 Visits: 7156 Method below works for any and all date and language settings:`DECLARE @startDate datetimeDECLARE @number_of_months intSET @startDate = GETDATE()SET @number_of_months = 7SELECT DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_monthFROM ( SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1 + month_offset, 0)) AS last_day_of_month FROM ( SELECT 0 AS month_offset 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 ) AS month_offsets WHERE month_offset BETWEEN 0 AND (@number_of_months - 1)) AS derivedORDER BY 1`Edit: It's not a "magic" number, of course, just a known Sunday. Then the only "assumption" needed for the code to work is every 7 days after that Sunday it will be Sunday again, which is about as safe an assumption as it gets :-). SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size] Michael Valentine Jones SSCertifiable Group: General Forum Members Points: 5774 Visits: 11771 ScottPletcher (2/11/2013)Method below works for any and all date and language settings:`DECLARE @startDate datetimeDECLARE @number_of_months intSET @startDate = GETDATE()SET @number_of_months = 7SELECT DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_monthFROM ( SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1 + month_offset, 0)) AS last_day_of_month FROM ( SELECT 0 AS month_offset 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 ) AS month_offsets WHERE month_offset BETWEEN 0 AND (@number_of_months - 1)) AS derivedORDER BY 1`Edit: It's not a "magic" number, of course, just a known Sunday. Then the only "assumption" needed for the code to work is every 7 days after that Sunday it will be Sunday again, which is about as safe an assumption as it gets :-).An issue with your code is that it does not work with dates before 1900-01-06.That is why I used '17530107' in my code, which is really similar to your code, except that the only issue is with dates before '17530107' for which there is no non-null solution when you use SQL Server datetime.For example, try the code with this:`SET @startDate = '18470228'` ScottPletcher SSCertifiable Group: General Forum Members Points: 7931 Visits: 7156 [superceded by next post] SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size] ScottPletcher SSCertifiable Group: General Forum Members Points: 7931 Visits: 7156 ScottPletcher (2/11/2013)Michael Valentine Jones (2/11/2013)[quote]ScottPletcher (2/11/2013)Method below works for any and all date and language settings:`DECLARE @startDate datetimeDECLARE @number_of_months intSET @startDate = GETDATE()SET @number_of_months = 7SELECT DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_monthFROM ( SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1 + month_offset, 0)) AS last_day_of_month FROM ( SELECT 0 AS month_offset 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 ) AS month_offsets WHERE month_offset BETWEEN 0 AND (@number_of_months - 1)) AS derivedORDER BY 1`Edit: It's not a "magic" number, of course, just a known Sunday. Then the only "assumption" needed for the code to work is every 7 days after that Sunday it will be Sunday again, which is about as safe an assumption as it gets :-).An issue with your code is that it does not work with dates before 1900-01-06.That is why I used '17530107' in my code, which is really similar to your code, except that the only issue is with dates before '17530107' for which there is no non-null solution when you use SQL Server datetime.For example, try the code with this:`SET @startDate = '18470228'`I haven't been in any business that worked with dates before 1900, but if yours did, then that would be a relevant concern for you. Besides, we frequently use smalldatetime here, so no dates before 1900 would be valid anyway. I'm thinking the odds of a smalldatetime being used are vastly greater for most people than needing a date before 1900.The other difference being that I didn't hard-code the number of months, of course. SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size] Michael Valentine Jones SSCertifiable Group: General Forum Members Points: 5774 Visits: 11771 ScottPletcher (2/11/2013)[quote]ScottPletcher (2/11/2013)...I haven't been in any business that worked with dates before 1900, but if yours did, then that would be a relevant concern for you. Besides, we frequently use smalldatetime here, so no dates before 1900 would be valid anyway. I'm thinking the odds of a smalldatetime being used are vastly greater for most people than needing a date before 1900...It's true that dates before 1900 are rare in most applications, but it's no more work to code '17530107' than to code '19000107', so there is no real advantage to using '19000107' and for a public forum, I prefer the more general solution.