week days

Question

Post reply

week days

selvamariappan

Newbie

Points: 5
More actions
June 20, 2008 at 6:15 am

#192556

How to find the week days in a month using sql server 2005
thanks in advance
selva mariappan

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

How will you identify which month you want?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 2

The best way is use a calendar table.

If you don't want to do that, you'll get an approximate number (accurate in most, but not all cases), by using:

select datediff(day, date1, date2) - (2 * datediff(week, date1, date2))

With "date1" and "date2" being your start and end dates.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

GSquared (6/20/2008)
The best way is use a calendar table.

Heh... except on Fridays and every 3rd Tuesday.

I

f you don't want to do that, you'll get an approximate number (accurate in most, but not all cases), by using:
select datediff(day, date1, date2) - (2 * datediff(week, date1, date2))
With "date1" and "date2" being your start and end dates.

I don't think they're looking for a count of week days in a month... I think they want all of the dates. Of course, I could be wrong.

Either way, we still need to know how the desired month is going to be identified by the OP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 4

Jeff Moden (6/20/2008)
GSquared (6/20/2008)
The best way is use a calendar table.
Heh... except on Fridays and every 3rd Tuesday.

Yeah, it's weird how calendar tables work except on those days. 🙂

Seriously though, since the original post asked a general question, I gave a general answer. Sometimes that's enough.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

selvamariappan (6/20/2008)
How to find the week days in a month using sql server 2005
thanks in advance
selva mariappan

You never did identify how you were going to identify which month you wanted the dates for...

If you got your solution from another forum and they didn't use a Tally/Numbers/Calendar table or they didn't use a CTE to cross join a system table, then you probably got one of the slow answers 😉

Take a little time out, here... tell us how you're going to identify the month and we'll show you a way that'll be very hard to beat for performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

malawk Newbie Points: 5 More actions · Answer 6

This is how i do it; i use a tally table (its used for heaps of other things so you could limit to a smaller table if you really wanted to, so if you dont have a tally table, you can create one like this

select top 11000

identity(int,1,1) as n

into dbo.tally

from master.dbo.syscolumns sc1,

master.dbo.syscolumns sc2

then i create a function that creates a table containing all the days in the given month, strip saturdays and sundays, strip days greater tan today (in case of current month) and i even have a place holder for removing public holidays....

NOTE: i have used the 2008 declarations style (as mine is in 2008, but that can easily be converted by adding extra 'set' statements)...

-------------------------------------------------------------------------------------------------

--

-- Procedure Name: dbo.fn_GetBusinessDays

--

-- Example : select dbo.fn_GetBusinessDays('15 Oct 2010')

--

-- Description : Returns the number of business days (as an int) in a in a month

-- specified by the paramenter

--

-- Parameters : @p_MonthDate - the month you want to count the working days in (day part is irrelevant)

--

-- References : this is used by **********************************************

--

-------------------------------------------------------------------------------------------------

create function [dbo].[fn_GetBusinessDays]

(

@p_MonthDate datetime

)

returns int

as

begin

--===== declare temp for holding list of days

declare @tempTable table

(

theDate datetime not null

)

declare @StartOfMonth datetime = '1 ' + left(datename(mm, @p_MonthDate), 3) + ' ' + convert(varchar, datepart(yyyy, @p_MonthDate))

declare @EndOfMonth datetime = dateadd(s, -1, dateadd(mm, 1, @StartOfMonth))

declare @today datetime = getdate()

--===== use tally table to get full list of days in period

insert into @tempTable

(

theDate

)

select convert(datetime, convert(varchar, n) + ' ' + -- eg. '1 '

datename(mm, @StartOfMonth) + ' ' + -- eg. 'Oct '

convert(varchar,datepart(yy, @StartOfMonth))) -- eg. '2010'

from dbo.tally

where n <= day(@EndOfMonth)

--===== remove weekend days

delete

from @tempTable

where datename(dw, theDate) in ('Saturday', 'Sunday')

--===== remove days greater than today

delete

from @tempTable

where theDate > @today

--===== if you have a table with list of public holidays,

-- then subtract them from @tempTable here

--===== return

return

(

select count(1)

from @tempTable

)

end

-------------------------------------------------------------------------------------------------

--

-- Created by : Jason Franklin

-- Date : 9 Nov 2010

--

-- Modified by:

-- Date :

-- Comment :

-- Change No :

-- Code Marker:

--

-------------------------------------------------------------------------------------------------

hope it helps

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 7

selvamariappan (6/20/2008)
How to find the week days in a month using sql server 2005
thanks in advance
selva mariappan

There are a couple of solutions on this thread now. I'd respond except you've not answered my original question which was... what will you use to identify which month you want?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)