weekday - 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)

weekday

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2008-01-11 : 10:58:29

How to retrun weekday between to date?
for example
select datediff(d, '1/1/2007','1/8/2007')
it returns 7
but I want it to return 5.
Thanks.
Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 11:17:01

Refer this link for UDF:-

[url]http://www.mssqlcity.com/FAQ/Devel/get_working_days.htm[/url]

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-11 : 11:22:38

SELECT SUM(CASE WHEN DATEPART(w, Date) IN (2, 3, 4, 5, 6) THEN 1 ELSE 0 END) AS Weekdays FROM (
SELECT CAST(n0 + n1 + n2 + n3 + n4 + n5 + n6 + n7 AS DATETIME) AS Date
FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1
CROSS JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2
CROSS JOIN (SELECT 0 AS n3 UNION SELECT 64 UNION SELECT 128 UNION SELECT 192) AS z3
CROSS JOIN (SELECT 0 AS n4 UNION SELECT 256 UNION SELECT 512 UNION SELECT 768) AS z4
CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1024 UNION SELECT 2048 UNION SELECT 3072) AS z5
CROSS JOIN (SELECT 0 AS n6 UNION SELECT 4096 UNION SELECT 8192 UNION SELECT 12288) AS z6
CROSS JOIN (SELECT 0 AS n7 UNION SELECT 16384 UNION SELECT 32768 UNION SELECT 49152) AS z7 ) Datedim
WHERE Date >= '20070101' AND Date < '20070108'

viguro
Starting Member

12 Posts

Posted - 2008-01-11 : 16:05:23

CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))
GO

SET NOCOUNT ON
DECLARE @FirstSat datetime, @x int
SELECT @FirstSat = '1/6/2007', @x = 1

--Add WeekEnds
WHILE @x < 52
BEGIN
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT DATEADD(ww,@x,@FirstSat), 'SAT' UNION ALL
SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN'
SELECT @x = @x + 1
END
SET NOCOUNT OFF
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO

-- Add US Holidaze
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT '1/1/2007', 'THU' UNION ALL
SELECT '3/21/2007', 'MON' UNION ALL
SELECT '5/21/2007', 'MON' UNION ALL
SELECT '7/2/2007', 'MON' UNION ALL
SELECT '8/6/2007', 'MON' UNION ALL
SELECT '9/3/2007', 'THU' UNION ALL
SELECT '10/8/2007', 'FRI' UNION ALL
SELECT '12/25/2007', 'FRI' UNION ALL
SELECT '12/26/2007', 'FRI'
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO

-- WeekDays in May

DECLARE @Start datetime, @End datetime
SELECT @Start = '5/1/2007', @End = '5/31/2007'

SELECT DATEDIFF(dd,@Start,@End)- COUNT(*) AS WeekDays
FROM WeekEndsAndHolidays
WHERE DayOfWeekDate BETWEEN @Start AND @End
GO

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-01-12 : 18:30:02

Create a function that uses the following formula... of course, I used English days which you may want to change... you could also base it on @@FirstDay or 01/01/1900 to make it more universal...

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources