# Get Date From From Calendar As Per Work Day

#1

Hi ,

I am new to T-SQL , i need to to extract the date from calendar as per work day with below conditions :

1. Week starts from Saturday
2. There are 6 days in a week as Friday will not be counted .

Example :

Salesman-1 have a journey plan for Store-X on SATURDAY, MONDAY & WEDNESDAY , Whereas FRIDAY there will be no visit.

Workday's are : 1 , 3 , 5 , 7 , ...

Where : 1 = Saturday (Week Start) , 2 = Sunday , 3 = Monday , 4 = Tuesday ,
5 = Wednesday, 6 = Thursday , 7 = Saturday ( Friday will be skipped )

So the outcome of the query should be :
Saturday ( 07.Oct-2017 ) ,
Monday ( 09.Oct-2017 ) ,
Wednesday ( 11.Oct-2017 ) ,
Saturday ( 14.Oct-2017 )

Regards,

#2

This returns the dates for as many days as you pass in:

``````CREATE FUNCTION dbo.itfDates(@Start date,@Days int) RETURNS table AS
RETURN (
WITH l1 (n) AS (SELECT 0 n UNION ALL SELECT 0)
, l2 (n) AS (SELECT a.n FROM l1 a CROSS JOIN l1 b )
, l3 (n) AS (SELECT a.n FROM l2 a CROSS JOIN l2 b )
, l4 (n) AS (SELECT a.n FROM l3 a CROSS JOIN l3 b )
, l5 (n) AS (SELECT a.n FROM l4 a CROSS JOIN l4 b )
, l6 (n) AS (SELECT a.n FROM l5 a CROSS JOIN l5 b )
, l7 (n) AS (SELECT a.n FROM l6 a CROSS JOIN l6 b )
, l8 (n) AS (SELECT a.n FROM l7 a CROSS JOIN l7 b )
, Nums (n) AS (SELECT Row_Number() OVER (ORDER BY n) FROM l8 UNION SELECT 0)
SELECT DateAdd(dd, Nums.n, @Start) [Date] FROM nums WHERE n < @Days
);
GO``````

then to get the days without Fridays:

``````SELECT [Date], DateName(dw, [Date]) WeekDayName
FROM dbo.itfDates('20171012', 14)
WHERE DatePart(dw, [Date]) <> 6 ;``````

#3