SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


To Find the First Saturday of any month


To Find the First Saturday of any month

Author
Message
Jackarun
Jackarun
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 54
Comments posted to this topic are about the item To Find the First Saturday of any month
JMasciantoni
JMasciantoni
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 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
Alphonse
Alphonse
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 602
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
JMasciantoni
JMasciantoni
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 15
Alphonse,
Thanks for the quick post. This is a very clean solution.
John
anand.ramanan
anand.ramanan
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 70
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



DavidH-768896
DavidH-768896
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 150
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


JMasciantoni
JMasciantoni
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 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
wildh
wildh
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 675
Why not try this -

SELECT @MONTHSTDATE = DATEADD(MONTH,DATEDIFF(MONTH,0,@STARTDATE),0)

instead of a long winded convert.



nigel.
nigel.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3201 Visits: 2907
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

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

nigel.
nigel.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3201 Visits: 2907
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search