RE: Last Sunday of a month in sql

SSC Guru

Points: 64818

February 11, 2013 at 5:53 pm

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 way
Michael 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)