SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jack / To Find the First Saturday of any month / Latest Posts

RE: To Find the First Saturday of any month

nigel. — Thu, 13 Aug 2009 07:42:31 GMT

[quote][b]gerald.drouin (8/13/2009)[/b][hr]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.[code="sql"]declare @DayToFind tinyint, @AnyDayInAMonth datetimeselect @DayToFind = 1, --Monday @AnyDayInAMonth = '2009-04-15' --Target monthselect dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind + 14) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))[/code][/quote]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

RE: To Find the First Saturday of any month

gerald.drouin — Thu, 13 Aug 2009 06:12:32 GMT

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.[code="sql"]declare @DayToFind tinyint, @AnyDayInAMonth datetimeselect @DayToFind = 1, --Monday @AnyDayInAMonth = '2009-04-15' --Target monthselect dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind + 14) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))[/code]

RE: To Find the First Saturday of any month

nigel. — Thu, 13 Aug 2009 02:10:30 GMT

[quote][b]gerald.drouin (8/12/2009)[/b][hr][code="sql"]declare @DayToFind tinyint, @AnyDayInAMonth datetimeselect @DayToFind = 1, --Monday @AnyDayInAMonth = '2009-04-15' --Target monthselect dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))[/code]Seems like a nice single-select statement to me.[/quote]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

RE: To Find the First Saturday of any month

wildh — Thu, 13 Aug 2009 01:59:57 GMT

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.

RE: To Find the First Saturday of any month

gerald.drouin — Wed, 12 Aug 2009 14:15:28 GMT

[code="sql"]declare @DayToFind tinyint, @AnyDayInAMonth datetimeselect @DayToFind = 1, --Monday @AnyDayInAMonth = '2009-04-15' --Target monthselect dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))[/code]Seems like a nice single-select statement to me.

RE: To Find the First Saturday of any month

DavidH-768896 — Wed, 12 Aug 2009 09:08:27 GMT

Optimally, in SQL 2008, we would declare @dt as a [b]date[/b] type instead of [b]datetime[/b] and there would be no time issue. Then we would remove the CONVERT from the PRINT statement and the result would be a true [b]date [/b]type instead of [b]varchar[/b].

RE: To Find the First Saturday of any month

nigel. — Wed, 12 Aug 2009 08:53:37 GMT

[quote][b]publicdh-tech (8/12/2009)[/b][hr]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[/quote]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

RE: To Find the First Saturday of any month

DavidH-768896 — Wed, 12 Aug 2009 08:33:58 GMT

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.[code="sql"]DECLARE @dt datetimeSET @dt = GetDate()-- Change to first of the monthSET @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)[/code]David

RE: To Find the First Saturday of any month

nigel. — Wed, 12 Aug 2009 01:57:26 GMT

[quote][b]wildh (8/12/2009)[/b][hr]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.[/quote]Wildh,Good point, I'd overlooked that. Nigel

RE: To Find the First Saturday of any month

nigel. — Wed, 12 Aug 2009 01:54:28 GMT

[quote][b]anand.ramanan (8/11/2009)[/b][hr]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.[/quote]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.

RE: To Find the First Saturday of any month

wildh — Wed, 12 Aug 2009 01:02:57 GMT

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.

RE: To Find the First Saturday of any month

anand.ramanan — Tue, 11 Aug 2009 10:50:46 GMT

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[code]declare @dw intset @dw = 7 -- [Sunday - Saturday] == [1 - 7] declare @d datetimeset @d = '7/21/2009' declare @first datetimeset @first = DATEADD(d,1-DAY(@d),@d) declare @dayofweek intset @dayofweek = DATEPART(dw,@first) print @first + (7+(@dw - @dayofweek))%7[/code]

RE: To Find the First Saturday of any month

nigel. — Tue, 11 Aug 2009 09:59:32 GMT

[quote][b]publicdh-tech (8/11/2009)[/b][hr]I still prefer the following to get to the first of the month:[code="sql"]SET @dt = DATEADD(d,1-DAY(@dt),@dt)[/code]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.[/quote]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

RE: To Find the First Saturday of any month

DavidH-768896 — Tue, 11 Aug 2009 09:36:12 GMT

I still prefer the following to get to the first of the month:[code="sql"]SET @dt = DATEADD(d,1-DAY(@dt),@dt)[/code]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.[i]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[/i](from Code Complete, 2nd Edition, [url]www.cc2e.com[/url])

RE: To Find the First Saturday of any month

nigel. — Tue, 11 Aug 2009 09:32:47 GMT

Just worked out an alternative that avoids one of the the modulus operations[code="sql"]SELECT firstsaturday = DATEADD(dd, (14 - @@DATEFIRST - DATEPART(dw,DATEADD(month,DATEDIFF(mm,0,somedate),0)))%7, DATEADD(month,DATEDIFF(mm,0,somedate),0))[/code]

RE: To Find the First Saturday of any month

nigel. — Tue, 11 Aug 2009 03:24:15 GMT

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. [code="sql"]SELECT firstsaturday = DATEADD(dd,(7 - (DATEPART(dw,DATEADD(month,DATEDIFF(mm,0,getdate()),0)) + @@DATEFIRST) % 7) % 7,DATEADD(month,DATEDIFF(mm,0,getdate()),0))[/code]If anyone needs further explanation I'll try and get back to you later.Hope this helpsNigel

RE: To Find the First Saturday of any month

wildh — Tue, 11 Aug 2009 01:29:02 GMT

Why not try this -SELECT @MONTHSTDATE = DATEADD(MONTH,DATEDIFF(MONTH,0,@STARTDATE),0)instead of a long winded convert.

RE: To Find the First Saturday of any month

JMasciantoni — Mon, 10 Aug 2009 12:27:56 GMT

It just keeps getting gooder and gooder :-)JohnAnd now for those that want a stored proc:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- 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=NULLASBEGIN 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 FirstTargetDOWEND

RE: To Find the First Saturday of any month

DavidH-768896 — Mon, 10 Aug 2009 12:13:00 GMT

Here is another approach with the advantage of restoring the @@DATEFIRST value after your calculation:[code="sql"]DECLARE @dt datetime, @date1st int-- Save @@DATEFIRST so we can restore it laterSET @date1st = @@DATEFIRSTSET DATEFIRST 7SET @dt = '10/12/2009'-- Change to first of the monthSET @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 DATEFIRSTSET DATEFIRST @date1st[/code]

RE: To Find the First Saturday of any month

anand.ramanan — Mon, 10 Aug 2009 11:12:41 GMT

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 [b]first Saturday[/b]Here is a slightly modified version which relies on the default datefirst which is 7[code]declare @dw intset @dw = 2 -- [Sunday - Saturday] == [1 - 7] declare @d datetimeset @d = '7/21/2009'declare @first datetimeset @first = convert(varchar, datepart(mm, @d)) + '/01/' + convert(varchar,datepart(yyyy, @d))declare @dayofweek intset @dayofweek = DATEPART(dw,@first)print @first + (7+(@dw - @dayofweek))%7[/code]

RE: To Find the First Saturday of any month

JMasciantoni — Mon, 10 Aug 2009 06:50:40 GMT

Alphonse,Thanks for the quick post. This is a very clean solution.John

RE: To Find the First Saturday of any month

Alphonse — Mon, 10 Aug 2009 06:37:04 GMT

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 DATETIMESET DATEFIRST 6SET @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 @MONTHSTDATEPRINT DATEPART(DW,@MONTHSTDATE)SET @GETDATE=@MONTHSTDATE - CASE WHEN DATEPART(DW,@MONTHSTDATE) = 1 THEN 0 ELSE (DATEPART(DW,@MONTHSTDATE)-8) ENDPRINT @GETDATE

RE: To Find the First Saturday of any month

JMasciantoni — Mon, 10 Aug 2009 06:20:44 GMT

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

To Find the First Saturday of any month

Jackarun — Thu, 30 Jul 2009 09:12:12 GMT

Comments posted to this topic are about the item [B]To Find the First Saturday of any month[/B]