dwain.c (2/11/2013)
Michael Valentine Jones (2/11/2013)
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())
) a
order by
a.DT
Your code, "DwainsWay", is sensitive to the setting of DATEFIRST ...
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.
dwain.c (2/11/2013)
If you don't like magic numbers, I think[/i] you can do it this wayMichael Valentine Jones (2/11/2013)
...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'
Could you please explain why you think it is sensitive to language? I didn't see anything in testing to support it.
Here is the test below that I ran to demo the impact of a non-us english setting for language.
set language 'english'
go
set language 'spanish'
go
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())
) a
order by
a.DT
Results:
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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2014-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)