Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL:  Date Function::

Posted on 2012-08-13
4
Medium Priority
?
824 Views
Last Modified: 2012-08-13
We have quite a few options for taking a date apart in SQL 2008 R2.  Is there a clean way of counting the number of week days in between 2 dates?
thx

JohnE
0
Comment
Question by:John Esraelo
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38288640
select datediff (DW, '20120801', '20120806' )
0
 

Author Comment

by:John Esraelo
ID: 38288670
True and I must apologize.  The real question would have been; the number of working days in a week.
The datediff will return the same number as if you were using DW or DAY or even D.
So, right now I am building a loop to go over the DWs in a range and filter out the Sat and Suns.
I will post it when done.
thx

JohnE
Unless of course someone already has that, and in that case, it would be sterling.
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 38288812
Try this
DECLARE 
	@dtstart DATETIME,
	@dtend DATETIME
SET @dtstart = '20120801' 
SET @dtend = '20120930'

SELECT 
    SUM(CASE WHEN DATENAME(WEEKDAY,DATEADD(DAY,Number,DATEADD(MONTH,DATEDIFF(MONTH,0,@dtstart),0)))
    		IN('Monday','Tuesday','Wednesday','Thursday','Friday') THEN 1
    ELSE 
		0 
    END) AS [Days]
FROM [master].dbo.[spt_values]
WHERE type = 'P'
AND DATEADD(DAY, Number, DATEADD(MONTH,DATEDIFF(MONTH,0,@dtstart),0))
    	< DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dtend),0))

Open in new window

0
 

Author Comment

by:John Esraelo
ID: 38288834
This is very good and I also finished my version:  see below:  I am giving you the credit of course for the work / solution.
-- ================================================
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,John Esraelo>
-- Create date: <Create Date, ,201208131014>
-- Description:	<Description, ,USAGE: PASS 2 DATES AND GET THE WORKING DAYS COUNT BACK --> EXCLUDING THE HOLIDAYS AND DAYS OFF>
-- SELECT DBO.UDF_GetWorkDaysCount_Range ('2012-08-01 08:00:00', GETDATE())
-- =============================================
ALTER FUNCTION UDF_GetWorkDaysCount_Range 
(
		 @FirstDate as datetime 
		,@SecondDate as datetime
)
RETURNS int
AS
BEGIN
-- IF THE SECOND DATE IS PRIOR TO THE FIRST DATE THEN CALCULATE THE DELTA FOR FIRST DATE AND NOW GETDATE()
		IF @FirstDate > @SecondDate
			SET @SecondDate = GETDATE() 
			
		declare @Counter int, @CountTheDays int
		declare @Interval  int
		 
		set @Counter = 0
		set @CountTheDays = 0

    	set @Interval = (select DATEDIFF(DAY, @firstdate, @seconddate)) 

		while (@Counter <= @Interval )
		begin
			if DATEPART(DW, @FirstDate+@Counter) in (2,3,4,5,6) 
				set @CountTheDays += 1
			set @counter +=1
		end

	RETURN (@CountTheDays)

END
GO

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Stellar Exchange Toolkit: this 5 in 1 toolkit comes loaded with mega-software tool. Here’s an introduction to tools’ usage and advantages:
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question