Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Last Sunday of a month in sql Expand / Collapse
Author
Message
Posted Saturday, February 9, 2013 3:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, 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
Post #1418065
Posted Saturday, February 9, 2013 4:35 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:38 AM
Points: 3,110, Visits: 11,528
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


Post #1418067
Posted Monday, February 11, 2013 2:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 3,428, Visits: 5,381
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!
Post #1418272
Posted Monday, February 11, 2013 3:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
Thanks all for your reply....Shall try and let you know guys!
Post #1418282
Posted Monday, February 11, 2013 7:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:38 AM
Points: 3,110, Visits: 11,528
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'



Post #1418437
Posted Monday, February 11, 2013 8:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 2,325, Visits: 3,502
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1418491
Posted Monday, February 11, 2013 9:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:38 AM
Points: 3,110, Visits: 11,528
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'


Post #1418509
Posted Monday, February 11, 2013 9:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 2,325, Visits: 3,502
[superceded by next post]

SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1418528
Posted Monday, February 11, 2013 9:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 2,325, Visits: 3,502
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1418529
Posted Monday, February 11, 2013 3:21 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:38 AM
Points: 3,110, Visits: 11,528
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
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse