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

1 Like
Yes it works fine. Thank you @harishgg1