Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Last Sunday of a month in sql
Add to briefcase
25 posts, Page 1 of 3
123»»»

Last Sunday of a month in sql Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
var05
Posted Saturday, February 09, 2013 3:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:13 AM
Points: 83, Visits: 362
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
Michael Valentine Jones
Posted Saturday, February 09, 2013 4:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481
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
dwain.c
Posted Monday, February 11, 2013 2:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158
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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1418272
var05
Posted Monday, February 11, 2013 3:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:13 AM
Points: 83, Visits: 362
Thanks all for your reply....Shall try and let you know guys!
Post #1418282
Michael Valentine Jones
Posted Monday, February 11, 2013 7:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481
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
ScottPletcher
Posted Monday, February 11, 2013 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 1,318, Visits: 1,763
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)
One man with courage makes a majority. Andrew Jackson
Post #1418491
Michael Valentine Jones
Posted Monday, February 11, 2013 9:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481
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
ScottPletcher
Posted Monday, February 11, 2013 9:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 1,318, Visits: 1,763
[superceded by next post]

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1418528
ScottPletcher
Posted Monday, February 11, 2013 9:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 1,318, Visits: 1,763
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)
One man with courage makes a majority. Andrew Jackson
Post #1418529
Michael Valentine Jones
Posted Monday, February 11, 2013 3:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481
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
25 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.