SQL query 4 weeks 3 business days

Question

SQL query 4 weeks 3 business days

DaveDB

SSCrazy

Points: 2173
More actions
March 11, 2013 at 9:19 am

#271373

Hello,
I need to write a query that returns a result set that is within the past 4 weeks and 3 business days. For example, the result set for today would return everything from Feb 6th forward. (Date of this post was March 11).
I have the second part but I am not sure how to include the 3 days. Any help would be appreciated.
select dateadd(week,-4,getdate());
-Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

David Mando (3/11/2013)
Hello,
I need to write a query that returns a result set that is within the past 4 weeks and 3 business days. For example, the result set for today would return everything from Feb 6th forward. (Date of this post was March 11).
I have the second part but I am not sure how to include the 3 days. Any help would be appreciated.
select dateadd(week,-4,getdate());
-Dave

4 calendar weeks Monday to Friday is possible but for "3 business days" you will need a calendar table, as there is no universal definition for the "business day". For example, today, 11th March 2013, is business day in UK, but bank holiday in Victoria - state of Australia (local 1st May :-)!).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

DaveDB SSCrazy Points: 2173 More actions · Answer 2

Thanks so much for your quick response.

How would I write this query if it is just (- 4 weeks and 3 days)?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Calendar weeks or working weeks?

What is your definition of a week? Is it Monday To Friday? Is it Monday to Sunday?

Is it Sunday to Saturday? Or may be even (as in some middle-east countries) Saturday to Wednesday?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ScottPletcher SSC Guru Points: 101103 More actions · Answer 4

David Mando (3/11/2013)
Thanks so much for your quick response.
How would I write this query if it is just (- 4 weeks and 3 days)?

select dateadd(day, -3, dateadd(week,-4,getdate()));

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".