SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Last Sunday of a month in sql


Last Sunday of a month in sql

Author
Message
var05
var05
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

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
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

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())
) a
order 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.000
2013-01-09 18:30:40.447 2013-01-31 00:00:00.000 2013-01-27 00:00:00.000
2013-02-09 18:30:40.447 2013-02-28 00:00:00.000 2013-02-24 00:00:00.000
2013-03-09 18:30:40.447 2013-03-31 00:00:00.000 2013-03-31 00:00:00.000
2013-04-09 18:30:40.447 2013-04-30 00:00:00.000 2013-04-28 00:00:00.000
2013-05-09 18:30:40.447 2013-05-31 00:00:00.000 2013-05-26 00:00:00.000
2013-06-09 18:30:40.447 2013-06-30 00:00:00.000 2013-06-30 00:00:00.000
2013-07-09 18:30:40.447 2013-07-31 00:00:00.000 2013-07-28 00:00:00.000
2013-08-09 18:30:40.447 2013-08-31 00:00:00.000 2013-08-25 00:00:00.000
2013-09-09 18:30:40.447 2013-09-30 00:00:00.000 2013-09-29 00:00:00.000
2013-10-09 18:30:40.447 2013-10-31 00:00:00.000 2013-10-27 00:00:00.000
2013-11-09 18:30:40.447 2013-11-30 00:00:00.000 2013-11-24 00:00:00.000
2013-12-09 18:30:40.447 2013-12-31 00:00:00.000 2013-12-29 00:00:00.000
2014-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
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

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())
) a
order 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
var05
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 437
Thanks all for your reply....Shall try and let you know guys!
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

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())
) a
order 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
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7931 Visits: 7156
Method below works for any and all date and language settings:



DECLARE @startDate datetime
DECLARE @number_of_months int

SET @startDate = GETDATE()
SET @number_of_months = 7

SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_month
FROM (
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 derived
ORDER 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
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

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 datetime
DECLARE @number_of_months int

SET @startDate = GETDATE()
SET @number_of_months = 7

SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_month
FROM (
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 derived
ORDER 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
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

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
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

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 datetime
DECLARE @number_of_months int

SET @startDate = GETDATE()
SET @number_of_months = 7

SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_month
FROM (
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 derived
ORDER 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
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search