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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Go Back 5 business Days

Seeing if anyone knows how to query back 5 business days?

business days meaning no weekends.

So if I ran today(12/19) it would return a date of 12/13/2013
0
patriotpacer
Asked:
patriotpacer
1 Solution
 
Scott Fell, EE MVEDeveloperCommented:
In your where clause can you use

 WHERE DATEPART(dw,'<<some date>>') <> 1 AND DATEPART(dw,'<<some date>>') <> 7
0
 
vr6rCommented:
DECLARE @daysback INT = 5

WHILE(DATEPART(DW, GETDATE() - @daysback) IN (1,7))
BEGIN
	SET @daysback = @daysback + 1
END

SELECT CONVERT(DATE,GETDATE() - @daysback, 101)

Open in new window

0
 
Scott PletcherSenior DBACommented:
Easiest is to first create a "NonWorkDays" table.  Put in all nonwork days, including weekends and holidays.

Then you can do something like this:

DECLARE @start_date datetime
DECLARE @num_of_days_back int

SET @start_date = GETDATE()
SET @num_of_days_back = 5

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
)
SELECT TOP (1) date_to_check
FROM (
    SELECT TOP (@num_of_days_back) date_to_check
    FROM cteTally t
    CROSS APPLY (
        SELECT DATEADD(DAY, -t.tally, @start_date) AS date_to_check
    ) AS ca1
    WHERE
        t.tally BETWEEN 0 AND @num_of_days_back * 4 AND
        NOT EXISTS(SELECT 1 FROM dbo.NonWorkDays nwd WHERE nwd.NonWorkDay = date_to_check)
    ORDER BY date_to_check DESC
) AS possible_dates
ORDER BY date_to_check ASC
0
 
John_VidmarCommented:
declare	@dateStart	datetime
,	@dateEnd	datetime
,	@counter	int

-- chop off time, initialize variable
select	@dateStart	= convert(varchar,current_timestamp,101)
,	@dateEnd	= convert(varchar,current_timestamp,101)
,	@counter	= 1

-- go back 5 days
While @counter <= 5 begin
	select	@dateStart	= dateadd(dd,-1,@dateStart)
	,	@counter	= @counter + case when datepart(weekday,@dateStart) in (1,7) then 0 else 1 end
end

-- show results
select	@dateStart	
,	@dateEnd	

Open in new window

0
 
patriotpacerAuthor Commented:
Thank you so much for all the contributions.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now