|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 2:19 AM
Points: 269,
Visits: 50
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, April 16, 2010 4:42 AM
Points: 4,
Visits: 15
|
|
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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 7,
Visits: 414
|
|
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=@MONTHSTDATE - CASE WHEN DATEPART(DW,@MONTHSTDATE) = 1 THEN 0 ELSE (DATEPART(DW,@MONTHSTDATE)-8) END PRINT @GETDATE
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, April 16, 2010 4:42 AM
Points: 4,
Visits: 15
|
|
Alphonse, Thanks for the quick post. This is a very clean solution. John
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:40 PM
Points: 39,
Visits: 64
|
|
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
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Monday, May 06, 2013 2:15 PM
Points: 95,
Visits: 127
|
|
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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, April 16, 2010 4:42 AM
Points: 4,
Visits: 15
|
|
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 DATETIME, @MonthDate DATETIME, @DayLast INT SET @DayLast = @@DATEFIRST SET DATEFIRST @DayofWeek SELECT @MonthDate = CONVERT(DATETIME, CONVERT(VARCHAR(5),DATEPART(MM, @StartDate)) + '/01/' + CONVERT(VARCHAR(5),DATEPART(YYYY, @StartDate)) +' ' + '00:00:00 AM') SET @GetDate = @MonthDate - CASE WHEN (DATEPART(DW, @MonthDate) = 1) THEN 0 ELSE (DATEPART(DW,@MonthDate)-8) END
SET DATEFIRST @DayLast
SELECT @MonthDate AS FirstDayofMonth, @GetDate AS FirstTargetDOW
END
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 7:08 AM
Points: 292,
Visits: 657
|
|
Why not try this -
SELECT @MONTHSTDATE = DATEADD(MONTH,DATEDIFF(MONTH,0,@STARTDATE),0)
instead of a long winded convert.
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: 2 days ago @ 7:31 AM
Points: 1,164,
Visits: 2,601
|
|
No need to change DATEFIRST, create intermediate variables, or a WHILE loop . 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
-- Nigel Useful Links: How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: 2 days ago @ 7:31 AM
Points: 1,164,
Visits: 2,601
|
|
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))
-- Nigel Useful Links: How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden
|
|
|
|