InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:55:25 GMT20http://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]russell-154600 (2/13/2013)[/b][hr]Any argument that dates before 1900 are unlikely can't really be taken seriously can it? Pure foolishness.Please put all of the US Presidential elections and their results into a database.Please put all MLB batting statistics (since records were consistently kept) into a database.Please put the amendment,s along with ratification dates, to the US Constitution into a database.[/quote]Then 17530107 is pure foolishness too. Please put Caesar's date of death into a db. Please put the date of the Battle of Hastings into a db.It's absolutely foolish to drastically over-design something to hold values it will [b]NEVER[/b] need to hold.My business doesn't sell things in 1789 and never will.Wed, 13 Feb 2013 10:14:32 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxAny argument that dates before 1900 are unlikely can't really be taken seriously can it? Pure foolishness.Please put all of the US Presidential elections and their results into a database.Please put all MLB batting statistics (since records were consistently kept) into a database.Please put the amendment,s along with ratification dates, to the US Constitution into a database.Wed, 13 Feb 2013 10:00:05 GMTrussell-154600http://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]ScottPletcher (2/12/2013)[/b]If your perception is limited to only this one specific piece of code, then you will never understand what I'm talking about.I want a general approach that can be used safely in all situations.Again, I've [b]NEVER EVER[/b] had to work with a business date before 1900, and you haven't either, right? So you're accepting potential abends for NO possible gain under any circumstances ever.Good luck with that.[/quote][i]If your perception is limited to only this one specific piece of code, then you will never understand what I'm talking about.[/i]I have only posted about solutions to the question the OP asked. I cannot respond to imaginary flawed code that is somehow based on my code[i]"I want a general approach that can be used safely in all situations."[/i]My code produces correct results with all dates, yours does not.[i]"Again, I've [b]NEVER EVER[/b] had to work with a business date before 1900, and you haven't either, right? So you're accepting potential abends for NO possible gain under any circumstances ever."[/i]My code is no more complex that yours, there are no "potential abends" in the code I posted, and it is a general solution that produces correct results with any datetime or smalldatetime value. I have already challenged you to point out the imaginary flaws in my code, but so far: nothing.If you have some real criticism of my code based on the results of actual queries please post it.Tue, 12 Feb 2013 16:35:48 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxif date is after 01/01/1900 then following should workdeclare @d datetimeset @d='04/29/2013'select dateadd(day,-(datepart(dw,dateadd(month,datediff(month,-1,@d),-1))-1),dateadd(month,datediff(month,-1,@d),-1))Tue, 12 Feb 2013 15:20:51 GMTsqlbi.vvamsihttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]Michael Valentine Jones (2/12/2013)[/b][hr][quote][b]ScottPletcher (2/12/2013)[/b][hr][quote][b]Michael Valentine Jones (2/12/2013)[/b][hr][quote][b]ScottPletcher (2/12/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.[/quote]Not true: there's one [b]HUGE[/b] advantage to 19000101 and later: code with smalldatetimes [b]abend[/b] when using dates before 19000101.So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.[/quote]Did you test that? This seems to work OK for me:[code="sql"]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[/code]Results:[code="other"]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[/code][/quote]It's inherent in the very definition of a smalldatetime:[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect DATEDIFF(day, '175301017', @smalldatetime)[/code]Why you are so insistent on placing abend traps throughout your code for something that [i]never[/i] happens? I've [i]never[/i] worked on an any business item from 1899 or before.[/quote]The code I posted works fine with smalldatetime.If you can't be bothered to run the simple test script I posted to confirm that, I don't know what else I can do.Could you at least try to run this?[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')[/code][/quote]The one specific example you posted worked fine. Yes, I had a typo.DATEDIFF is doing some type of implicit conversion. I don't want to rely on that, and it certainly won't be true if, say, I try to set the base date into a column and then do the date functions on it.Thus, you never know when a smalldatetime is going to abend your code if you try to use '17530701' as a base date. Again, if you insist on adding abend traps into your code for absolutely no valid business reason whatsoever, go right ahead. But I won't do it and I will encourage others not to as well.[/quote]Well at least we can agree that my code works with all possible datetime or smalldatetime values, and yours only produces valid results with dates after 1900-01-01.As for the non-existent "abend traps", what can I say until you can actually demo one that impacts my code?[/quote]If your perception is limited to only this one specific piece of code, then you will never understand what I'm talking about.I want a general approach that can be used safely in all situations.Again, I've [b]NEVER EVER[/b] had to work with a business date before 1900, and you haven't either, right? So you're accepting potential abends for NO possible gain under any circumstances ever.Good luck with that.Tue, 12 Feb 2013 14:32:17 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]ScottPletcher (2/12/2013)[/b][hr][quote][b]Michael Valentine Jones (2/12/2013)[/b][hr][quote][b]ScottPletcher (2/12/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.[/quote]Not true: there's one [b]HUGE[/b] advantage to 19000101 and later: code with smalldatetimes [b]abend[/b] when using dates before 19000101.So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.[/quote]Did you test that? This seems to work OK for me:[code="sql"]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[/code]Results:[code="other"]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[/code][/quote]It's inherent in the very definition of a smalldatetime:[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect DATEDIFF(day, '175301017', @smalldatetime)[/code]Why you are so insistent on placing abend traps throughout your code for something that [i]never[/i] happens? I've [i]never[/i] worked on an any business item from 1899 or before.[/quote]The code I posted works fine with smalldatetime.If you can't be bothered to run the simple test script I posted to confirm that, I don't know what else I can do.Could you at least try to run this?[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')[/code][/quote]The one specific example you posted worked fine. Yes, I had a typo.DATEDIFF is doing some type of implicit conversion. I don't want to rely on that, and it certainly won't be true if, say, I try to set the base date into a column and then do the date functions on it.Thus, you never know when a smalldatetime is going to abend your code if you try to use '17530701' as a base date. Again, if you insist on adding abend traps into your code for absolutely no valid business reason whatsoever, go right ahead. But I won't do it and I will encourage others not to as well.[/quote]Well at least we can agree that my code works with all possible datetime or smalldatetime values, and yours only produces valid results with dates after 1900-01-01.As for the non-existent "abend traps", what can I say until you can actually demo one that impacts my code?Tue, 12 Feb 2013 14:02:54 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxSaw this snippet somewhere and thought it was a good solution:[code="sql"]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)[/code]Probably covers the 95% of tasks out there.Tue, 12 Feb 2013 13:12:39 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]Michael Valentine Jones (2/12/2013)[/b][hr][quote][b]ScottPletcher (2/12/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.[/quote]Not true: there's one [b]HUGE[/b] advantage to 19000101 and later: code with smalldatetimes [b]abend[/b] when using dates before 19000101.So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.[/quote]Did you test that? This seems to work OK for me:[code="sql"]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[/code]Results:[code="other"]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[/code][/quote]It's inherent in the very definition of a smalldatetime:[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect DATEDIFF(day, '175301017', @smalldatetime)[/code]Why you are so insistent on placing abend traps throughout your code for something that [i]never[/i] happens? I've [i]never[/i] worked on an any business item from 1899 or before.[/quote]The code I posted works fine with smalldatetime.If you can't be bothered to run the simple test script I posted to confirm that, I don't know what else I can do.Could you at least try to run this?[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')[/code][/quote]The one specific example you posted worked fine. Yes, I had a typo.DATEDIFF is doing some type of implicit conversion. I don't want to rely on that, and it certainly won't be true if, say, I try to set the base date into a column and then do the date functions on it.Thus, you never know when a smalldatetime is going to abend your code if you try to use '17530701' as a base date. Again, if you insist on adding abend traps into your code for absolutely no valid business reason whatsoever, go right ahead. But I won't do it and I will encourage others not to as well.Tue, 12 Feb 2013 13:01:23 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]ScottPletcher (2/12/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.[/quote]Not true: there's one [b]HUGE[/b] advantage to 19000101 and later: code with smalldatetimes [b]abend[/b] when using dates before 19000101.So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.[/quote]Did you test that? This seems to work OK for me:[code="sql"]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[/code]Results:[code="other"]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[/code][/quote]It's inherent in the very definition of a smalldatetime:[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect DATEDIFF(day, '175301017', @smalldatetime)[/code]Why you are so insistent on placing abend traps throughout your code for something that [i]never[/i] happens? I've [i]never[/i] worked on an any business item from 1899 or before.[/quote]The code I posted works fine with smalldatetime.If you can't be bothered to run the simple test script I posted to confirm that, I don't know what else I can do.Could you at least try to run this?[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect LastSundayofMonth = dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')[/code]Tue, 12 Feb 2013 12:06:51 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.[/quote]Not true: there's one [b]HUGE[/b] advantage to 19000101 and later: code with smalldatetimes [b]abend[/b] when using dates before 19000101.So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.[/quote]Did you test that? This seems to work OK for me:[code="sql"]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[/code]Results:[code="other"]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[/code][/quote]It's inherent in the very definition of a smalldatetime:[code="sql"]declare @smalldatetime smalldatetimeset @smalldatetime = GETDATE()select @smalldatetimeselect DATEDIFF(day, '175301017', @smalldatetime)[/code]Why you are so insistent on placing abend traps throughout your code for something that [i]never[/i] happens? I've [i]never[/i] worked on an any business item from 1899 or before.Tue, 12 Feb 2013 08:01:48 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxApparently changing the language from english to spanish changes the @@DATEFIRST value from 7 to 1.I didn't realize that side-effect. Interesting...It also accounts for why I didn't see it while testing as I was trying to test @@DATEFIRST setting (forcing it) at the same time I was testing the language change.Mon, 11 Feb 2013 19:05:05 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]dwain.c (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]dwain.c (2/11/2013)[/b][hr]If you don't like magic numbers, I think you can do it this way too:[code="sql"]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[/code][/quote]Your code, "DwainsWay", is sensitive to the setting of DATEFIRST ...[/quote]Indeed this is true that's why I said (with emphasis on think) the following. Sorry for being a bit too short on time to explain in detail.[quote][b]dwain.c (2/11/2013)[/b][hr]If you don't like magic numbers, I [b][i]think[/b][/i] you can do it this way[/quote][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr]...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.[code="sql"]set datefirst 4[/code][code="sql"]set language 'spanish'[/code][/quote]Could you please explain why you think it is sensitive to language? I didn't see anything in testing to support it.[/quote]Here is the test below that I ran to demo the impact of a non-us english setting for language.[code="sql"]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[/code]Results:[code="plain"]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)[/code]Mon, 11 Feb 2013 17:53:57 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.[/quote]Not true: there's one [b]HUGE[/b] advantage to 19000101 and later: code with smalldatetimes [b]abend[/b] when using dates before 19000101.So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.[/quote]Did you test that? This seems to work OK for me:[code="sql"]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[/code]Results:[code="other"]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[/code]Mon, 11 Feb 2013 17:42:11 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]dwain.c (2/11/2013)[/b][hr]If you don't like magic numbers, I think you can do it this way too:[code="sql"]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[/code][/quote]Your code, "DwainsWay", is sensitive to the setting of DATEFIRST ...[/quote]Indeed this is true that's why I said (with emphasis on think) the following. Sorry for being a bit too short on time to explain in detail.[quote][b]dwain.c (2/11/2013)[/b][hr]If you don't like magic numbers, I [b][i]think[/b][/i] you can do it this way[/quote][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr]...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.[code="sql"]set datefirst 4[/code][code="sql"]set language 'spanish'[/code][/quote]Could you please explain why you think it is sensitive to language? I didn't see anything in testing to support it.Also, here's a quite general solution based on a Calendar function, that in truth I'd probably be using. This one is sensitive to language.[code="sql"]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[/code]Here is the GenerateCalendar FUNCTION.[code="sql"] 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[/code]Mon, 11 Feb 2013 17:35:33 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.[/quote]Not true: there's one [b]HUGE[/b] advantage to 19000101 and later: code with smalldatetimes [b]abend[/b] when using dates before 19000101.So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.Mon, 11 Feb 2013 16:09:07 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b]...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...[/quote]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.Mon, 11 Feb 2013 15:21:08 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]ScottPletcher (2/11/2013)[/b][hr][quote][b]Michael Valentine Jones (2/11/2013)[/b][hr][quote][b]ScottPletcher (2/11/2013)[/b][hr]Method below works for any and all date and language settings:[code="sql"]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[/code]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 :-).[/quote]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:[code="sql"]SET @startDate = '18470228'[/code][/quote]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.Mon, 11 Feb 2013 09:49:19 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[superceded by next post]Mon, 11 Feb 2013 09:48:18 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]ScottPletcher (2/11/2013)[/b][hr]Method below works for any and all date and language settings:[code="sql"]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[/code]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 :-).[/quote]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:[code="sql"]SET @startDate = '18470228'[/code]Mon, 11 Feb 2013 09:17:55 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxMethod below works for any and all date and language settings:[code="sql"]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[/code]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 :-).Mon, 11 Feb 2013 08:50:20 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspx[quote][b]dwain.c (2/11/2013)[/b][hr]If you don't like magic numbers, I think you can do it this way too:[code="sql"]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[/code][/quote]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.[code="sql"]set datefirst 4[/code][code="sql"]set language 'spanish'[/code]Mon, 11 Feb 2013 07:45:36 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxThanks all for your reply....Shall try and let you know guys!Mon, 11 Feb 2013 03:30:57 GMTvar05http://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxIf you don't like magic numbers, I think you can do it this way too:[code="sql"]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[/code]Mon, 11 Feb 2013 02:59:02 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxFind the last day of the month, and then find the Sunday on or before that date.[code="sql"]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[/code]Results:[code="plain"]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[/code]Start of Week Function:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307[/url]Sat, 09 Feb 2013 16:35:03 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1418065-391-1.aspxHi 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?ThanksSat, 09 Feb 2013 15:48:23 GMTvar05