Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Last Sunday of a month in sql Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, February 09, 2013 3:48 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Saturday, February 01, 2014 2:14 PM Points: 100, Visits: 435
 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
Post #1418065
 Posted Saturday, February 09, 2013 4:35 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, April 16, 2014 1:11 PM Points: 3,081, Visits: 11,230
 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
Post #1418067
 Posted Monday, February 11, 2013 2:59 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 4:39 AM Points: 3,594, Visits: 5,105
 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!
Post #1418272
 Posted Monday, February 11, 2013 3:30 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Saturday, February 01, 2014 2:14 PM Points: 100, Visits: 435
Post #1418282
 Posted Monday, February 11, 2013 7:45 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, April 16, 2014 1:11 PM Points: 3,081, Visits: 11,230
 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'`
Post #1418437
 Posted Monday, February 11, 2013 8:50 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 7:38 AM Points: 1,746, Visits: 2,554
 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)I'm not fat, I'm gravity challenged.
Post #1418491
 Posted Monday, February 11, 2013 9:17 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, April 16, 2014 1:11 PM Points: 3,081, Visits: 11,230
 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'`
Post #1418509
 Posted Monday, February 11, 2013 9:48 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 7:38 AM Points: 1,746, Visits: 2,554
 [superceded by next post] SQL DBA,SQL Server MVP('07, '08, '09)I'm not fat, I'm gravity challenged.
Post #1418528
 Posted Monday, February 11, 2013 9:49 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 7:38 AM Points: 1,746, Visits: 2,554
 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)I'm not fat, I'm gravity challenged.
Post #1418529
 Posted Monday, February 11, 2013 3:21 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, April 16, 2014 1:11 PM Points: 3,081, Visits: 11,230
 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.
Post #1418654

 Permissions