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

To Find the First Saturday of any month Expand / Collapse
Author
Message
Posted Thursday, August 13, 2009 1:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:49 AM
Points: 292, Visits: 661
nigel,

That was what I was getting at.

publicdh-tech,

I like your point about 2008. Not being lucky enough to be using (or even testing) 2008 I'd not contemplated you can just use the date.



Post #769949
Posted Thursday, August 13, 2009 2:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:39 AM
Points: 1,181, Visits: 2,651
gerald.drouin (8/12/2009)
declare @DayToFind tinyint, @AnyDayInAMonth datetime
select @DayToFind = 1, --Monday
@AnyDayInAMonth = '2009-04-15' --Target month

select dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))

Seems like a nice single-select statement to me.


Gerald,

There are some issues with your solution which you'll see if you set @AnyDayInAMonth to '2009-08-01'.
The problem is with the expression:

datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind

which can yield a negative value in some cases.

But still good to see an attempt using @@DATEFIRST to make the solution universal, without using loops, and without the horrible constant that I had in my solution.

Nigel


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #769958
Posted Thursday, August 13, 2009 6:12 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 25, 2014 1:41 PM
Points: 176, Visits: 272
Thanks for the catch on the negative numbers. I've added a 14 day offset to ensure the code always lands in the current month. Works for every day in 2009 now.
declare @DayToFind tinyint, @AnyDayInAMonth datetime
select @DayToFind = 1, --Monday
@AnyDayInAMonth = '2009-04-15' --Target month
select dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind + 14) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))

Post #770079
Posted Thursday, August 13, 2009 7:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:39 AM
Points: 1,181, Visits: 2,651
gerald.drouin (8/13/2009)
Thanks for the catch on the negative numbers. I've added a 14 day offset to ensure the code always lands in the current month. Works for every day in 2009 now.
declare @DayToFind tinyint, @AnyDayInAMonth datetime
select @DayToFind = 1, --Monday
@AnyDayInAMonth = '2009-04-15' --Target month
select dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind + 14) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))



Gerald,

Yep, hopefully that does it. Was hoping to come up with a solution without that pesky constant but I've yet to find it. No doubt someone will, given the level of expertise here.

Nigel


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #770150
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse