Need to minus days from current day ignoring weekend

Transact-SQL
1

Hi Team, need your help to get the date by minus 3 days from currentdate ignoring sat and sun

For example: if today is Monday(4th may 2020) and i need to minus three days and get the date as thursday (30th apr 20)

If today is sunday (3rd May 20) and i need to minus 7 days and get the date as thurday(23rd apr 2020)

Number of days minus is kept configurable in the table

4

I have created below function to solve this

DECLARE @DAYS INT = 15;
DECLARE @ADDED INT = 0;
DECLARE @DATEADDED INT = 0;
DECLARE @DATE DATE;
DECLARE @DATEPART INT;
SET @DATEADDED = 0;
SET @ADDED = 0;

WHILE (@DATEADDED <= @DAYS)
BEGIN
SET @DATEPART = DATEPART(DW, DATEADD(D, -@ADDED, CONVERT(DATE,GETDATE())))
IF @DATEPART=1
BEGIN
SET @ADDED = @ADDED +2
SET @DATE = DATEADD(D, -@ADDED, CONVERT(DATE,GETDATE()))
END
ELSE IF @DATEPART=7
BEGIN
SET @ADDED = @ADDED +1
SET @DATE = DATEADD(D, -@ADDED, CONVERT(DATE,GETDATE()))
END
BEGIN
SET @DATE = DATEADD(D, -@ADDED, CONVERT(DATE,GETDATE()))
END
SET @ADDED= @ADDED+1
SET @DATEADDED = @DATEADDED + 1

END

5 
--create table #calendar(calendardate date)

;with src
as
(
	--SELECT  TOP (5000) 
	--		SomeDate       = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2010','2020')+CAST('2010' AS DATETIME)
        
	--   FROM      sys.all_columns t1 
	--  CROSS JOIN sys.all_columns t2 
	select distinct top 150 dateadd(dd,column_id * -1,getdate()) SomeDate 
	from sys.columns
	where column_id between 1 and 30  
	union
	select getdate()
	   
  )
--insert into #calendar
select distinct SomeDate,
                DATENAME(WEEKDAY,DATEADD(dd,-3,SomeDate)) as _weekDay
  from src  
  where DATENAME(weekday, DATEADD(dd,-3,SomeDate)) not in ('Sunday','Saturday')
  order by 1 desc
1 Like
7

hi

I made a correction !!! ( any comments .. please YES/NO/Maybe/CARROT ?? )

it should work now ..

-- format dd/mm/yyyy 
DECLARE @date DATE = '3/5/2020';

DECLARE @days_less INT = 7;

SELECT 'Date',@date 
SELECT 'Days Less',@days_less;

WITH tally_cte
AS (
       SELECT   TOP 365 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn  FROM sys.all_columns a CROSS JOIN sys.all_columns b
   )
     ,cte_rn 
AS (
       SELECT DATEADD(dd, -rn + 1, @date) AS dates , DATENAME(dw, DATEADD(dd, -rn + 1, @date)) AS wkname FROM tally_cte
   )
     , cte_withoutSATSUN
AS (
       SELECT   * FROM  cte_rn WHERE wkname NOT IN ( 'Sunday', 'Saturday' )
   )
     , cte_rownumber
AS (
       SELECT   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rnok, * FROM  cte_withoutSATSUN
   )
SELECT  'SQL Output',dates FROM  cte_rownumber WHERE rnok = @days_less
GO

image

1 Like
8

Yes it works fine. Thank you @harishgg1

9

Thank you @yosiasz