July 30, 2009 at 9:12 am
Comments posted to this topic are about the item To Find the First Saturday of any month
August 10, 2009 at 6:20 am
Jack,
Thanks!
In doing some testing I found that if the DOW you are looking for is also the first day of the month then it jumps to the next target DOW in the month. For an example try looking for the first Friday in January 2010.
John
August 10, 2009 at 6:37 am
I found the same problem. When the first of the month is the day in question, it returns the second week. I modified it to this.
DECLARE @DATE DATETIME,@GETDATE DATETIME,@MONTHSTDATE DATETIME,@STARTDATE DATETIME
SET DATEFIRST 6
SET @STARTDATE='06/21/2010'
SELECT @MONTHSTDATE = CONVERT(DATETIME, CONVERT(VARCHAR(5),DATEPART(MM, @STARTDATE)) + '/01/' + CONVERT(VARCHAR(5),DATEPART(YYYY, @STARTDATE)) +' ' + '00:00:00 AM')
PRINT @MONTHSTDATE
PRINT DATEPART(DW,@MONTHSTDATE)
SET @getdate-2=@MONTHSTDATE - CASE WHEN DATEPART(DW,@MONTHSTDATE) = 1 THEN 0
ELSE (DATEPART(DW,@MONTHSTDATE)-8)
END
PRINT @getdate-2
August 10, 2009 at 6:50 am
Alphonse,
Thanks for the quick post. This is a very clean solution.
John
August 10, 2009 at 11:12 am
Hi,
This is a nice idea, however, with the implementation I see an issue. What if I want to find the first Tuesday for july 2009? Instead of -8 I would have to do -4 and this lands me in June 30th 2009!?
Jack/Alphonse--I understand that your initial implementation was to get the first Saturday
Here is a slightly modified version which relies on the default datefirst which is 7
declare @DW int
set @DW = 2 -- [Sunday - Saturday] == [1 - 7]
declare @d datetime
set @d = '7/21/2009'
declare @first datetime
set @first = convert(varchar, datepart(mm, @d)) + '/01/' + convert(varchar,datepart(yyyy, @d))
declare @dayofweek int
set @dayofweek = DATEPART(dw,@first)
print @first + (7+(@dw - @dayofweek))%7
August 10, 2009 at 12:13 pm
Here is another approach with the advantage of restoring the @@DATEFIRST value after your calculation:
DECLARE @dt datetime, @date1st int
-- Save @@DATEFIRST so we can restore it later
SET @date1st = @@DATEFIRST
SET DATEFIRST 7
SET @dt = '10/12/2009'
-- 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)
-- Restore DATEFIRST
SET DATEFIRST @date1st
August 10, 2009 at 12:27 pm
It just keeps getting gooder and gooder 🙂
John
And now for those that want a stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- CREATED ON====================================================
--8/10/2009 , SQLServerCentral.com
-- DESCRIPTION=====================================================
--Find the first day of week of any month
--Example: Saturday is the 6th day of the week so we use 6 as the
-- @DateFirst variable.
--We want the first Saturday of July 2009 so enter ANY date in July 2009.
--The proc will return '07/04/2009' which is the first Saturday of July 2009
-- MODIFICATIONS=================================================
--==============================================================
CREATE PROCEDURE [dbo].[UT_First_DayOfWeek_Of_Month]
@DayofWeek INT=0,
@StartDate DATETIME=NULL
AS
BEGIN
DECLARE@Date DATETIME,
@getdate-2 DATETIME,
@MonthDate DATETIME,
@DayLast INT
SET@DayLast = @@DATEFIRST
SETDATEFIRST @DayofWeek
SELECT@MonthDate =
CONVERT(DATETIME, CONVERT(VARCHAR(5),DATEPART(MM, @StartDate))
+ '/01/' + CONVERT(VARCHAR(5),DATEPART(YYYY, @StartDate))
+' ' + '00:00:00 AM')
SET@getdate-2 =
@MonthDate -
CASE WHEN
(DATEPART(DW, @MonthDate) = 1)
THEN 0
ELSE (DATEPART(DW,@MonthDate)-8)
END
SETDATEFIRST @DayLast
SELECT@MonthDateAS FirstDayofMonth,
@getdate-2AS FirstTargetDOW
END
August 11, 2009 at 1:29 am
Why not try this -
SELECT @MONTHSTDATE = DATEADD(MONTH,DATEDIFF(MONTH,0,@STARTDATE),0)
instead of a long winded convert.
August 11, 2009 at 3:24 am
No need to change DATEFIRST, create intermediate variables, or a WHILE loop :w00t:.
Just a bit of arithmetic to work out the offset from the first of the month to the first Saturday, using @@DATEFIRST to normalize the day of the week.
SELECT firstsaturday = DATEADD(dd,(7 - (DATEPART(dw,DATEADD(month,DATEDIFF(mm,0,getdate()),0)) + @@DATEFIRST) % 7) % 7,DATEADD(month,DATEDIFF(mm,0,getdate()),0))
If anyone needs further explanation I'll try and get back to you later.
Hope this helps
Nigel
August 11, 2009 at 9:32 am
Just worked out an alternative that avoids one of the the modulus operations
SELECT firstsaturday = DATEADD(dd,
(14 - @@DATEFIRST - DATEPART(dw,DATEADD(month,DATEDIFF(mm,0,somedate),0)))%7,
DATEADD(month,DATEDIFF(mm,0,somedate),0))
August 11, 2009 at 9:36 am
I still prefer the following to get to the first of the month:
SET @dt = DATEADD(d,1-DAY(@dt),@dt)
Nigel, nice! Your arithmetic is very crafty and gets a solution in a single statement. But I always prefer code that is easily understood and maintainable by the programmers that come after me, even if there are a couple extra lines and temporary variables.
When I am working on a problem I never think about beauty. I think only how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong.
— R. Buckminster Fuller
(from Code Complete, 2nd Edition, www.cc2e.com)
August 11, 2009 at 9:59 am
publicdh-tech (8/11/2009)
I still prefer the following to get to the first of the month:
SET @dt = DATEADD(d,1-DAY(@dt),@dt)Nigel, nice! Your arithmetic is very crafty and gets a solution in a single statement. But I always prefer code that is easily understood and maintainable by the programmers that come after me, even if there are a couple extra lines and temporary variables.
Thanks.
A guess a few comments may solve the readability and maintainability issue (sorry didn't have the luxury of time) ;-).
The only problem with variables is that you can't have them in a view.
I like your method of calculating the first of the month, one minor issue is that it needs to refer to the date twice. Which is ok when that's a simple variable as in your example. It can become a bit hairy when the date itself is a more complex calculation.
Nigel
August 11, 2009 at 10:50 am
Nigel,
I think you missed my post on page 1. It has as a similar implementation, and it allows to find the first sunday - saturday of the month. I did not spend time modifying the calculation of the first of the month. Your implementation is nice though.
So here is the modified code applying the new first day of month logic
declare @DW int
set @DW = 7 -- [Sunday - Saturday] == [1 - 7]
declare @d datetime
set @d = '7/21/2009'
declare @first datetime
set @first = DATEADD(d,1-DAY(@d),@d)
declare @dayofweek int
set @dayofweek = DATEPART(dw,@first)
print @first + (7+(@dw - @dayofweek))%7
August 12, 2009 at 1:54 am
anand.ramanan (8/11/2009)
Nigel,I think you missed my post on page 1. It has as a similar implementation, and it allows to find the first sunday - saturday of the month. I did not spend time modifying the calculation of the first of the month. Your implementation is nice though.
Anand,
Only problem with your solution is that it relies on DATEFIRST having the default value of 7, as you mentioned in your first post.
Although I do like the option to adjust which day to calculate.
This can also be done in my solution by adjusting the constant value of 14. ie 15 will give you the first Sunday and 20 the first Friday etc.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply