Get first weekday of month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get first weekday of month

Author

Topic

bcanonica
Starting Member

35 Posts

Posted - 2009-10-01 : 09:39:26

Looked all over the net and different forums, but still can't seem to find an example of returning the first weekday of the month. Closest thing I found was this.


--returns first Monday of month
DATEADD(wk, DATEDIFF(wk, 0, dateadd(dd, 6 - datepart(day, @inputDate), @inputDate)), 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 09:41:09

quote:
Originally posted by bcanonica

Looked all over the net and different forums, but still can't seem to find an example of returning the first weekday of the month. Closest thing I found was this.

--returns first Monday of month
DATEADD(wk, DATEDIFF(wk, 0, dateadd(dd, 6 - datepart(day, @inputDate), @inputDate)), 0)

Do you want to get first sunday of the month?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-01 : 09:47:50

See http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

N 56°04'39.26"
E 12°55'05.63"

bcanonica
Starting Member

35 Posts

Posted - 2009-10-02 : 09:16:34

Let me re-word this. I am looking for the first business day of the month. Holidays aren't required, but would be nice. So if the first day of the month is a Sunday it would return Monday.

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-02 : 09:47:27

Hi bcanonica!

I just happened to have some code I wrote to dynamically compute date ranges that I tweaked to give you what I believe you're looking for. Holidays aren't compensated for though so it won't give you your "nice to have"

You can set the @inputDate variable to any date within any month and it will return the 1st business day of that month.

declare
@beginDate datetime
, @month int
, @day int
, @year int
, @strMonth varchar(2)
, @strYear varchar(4)
, @strDate varchar(10)
, @strBeginDate varchar(25)
, @x int
, @inputDate DATETIME

SET @inputDate = '11/17/2009'

set @x=1
select @strMonth = datepart(month,dateadd(month,0, @inputDate))
select @strYear = datepart(year,dateadd(month,0, @inputDate))
select @strDate = @strMonth + '/' + cast(@x as varchar(1)) + '/' + @strYear

set @strBeginDate = @strMonth + '/' + cast(@x as varchar(2)) + '/' + @strYear
set @beginDate = CONVERT(datetime, @strBeginDate)

SELECT CASE WHEN DATEPART(dw,@strBeginDate) = 1
THEN DATEADD(dd,1,@strBeginDate)
WHEN DATEPART(dw,@strBeginDate) = 7
THEN DATEADD(dd,2,@strBeginDate)
ELSE @strBeginDate END

Does this help you out at all???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-02 : 09:58:49

SELECT dbo.fnGetNthWeekdayOfMonth( {any day fo the month in question}, 1, 1)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources