August 12, 2009 at 1:57 am
wildh (8/12/2009)
One thing to be mindful with - SET @dt = DATEADD(d,1-DAY(@dt),@dt) is that when it's used with getdate() the result includes the time as well as the date.
Wildh,
Good point, I'd overlooked that.
Nigel
August 12, 2009 at 8:33 am
Nigel/Wildh,
In my original post (768063), I convert the result to style 101 (mm/dd/yyyy), so it doesn't matter if @dt has a time or not.
DECLARE @dt datetime
SET @dt = GetDate()
-- Change to first of the month
SET @dt = DATEADD(d,1-DAY(@dt),@dt)
IF DATEPART(dw,@dt) 7
-- If not already Saturday, add a day until it is Saturday
WHILE DATEPART(dw, @dt) 7
SET @dt = DATEADD(d,1,@dt)
PRINT CONVERT(nvarchar(30), @dt, 101)
David
August 12, 2009 at 8:53 am
publicdh-tech (8/12/2009)
Nigel/Wildh,In my original post (768063), I convert the result to style 101 (mm/dd/yyyy), so it doesn't matter if @dt has a time or not.
David
David,
That is true in your example. I think, however, Wildh was talking more generally about that method of finding the first of the month.
In many cases it may not matter at all that the time component is present, but there will be some when it does - (Sods Law). 🙂
Nigel
August 12, 2009 at 2:15 pm
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.
August 13, 2009 at 1:59 am
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.
August 13, 2009 at 2:10 am
gerald.drouin (8/12/2009)
declare @DayToFind tinyint, @AnyDayInAMonth datetimeselect @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:
[font="Courier New"] datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind[/font]
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
August 13, 2009 at 6:12 am
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))
August 13, 2009 at 7:42 am
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 datetimeselect @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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply