Solved

# Date query -- to find a day from 3 weeks prior

Posted on 2013-06-12
486 Views
Hi Experts,

I need your help in writing a date query to find out friday's date from 3 weeks prior when I run it for current date. And if the current date is friday then I need to get friday's date from 2 weeks prior.

For Example:

If I run the query for date: 2013-06-12, I should get the date: 2013-05-24.

If I run the query for date: 2013-06-14, I should get the date: 2013-05-31.

Thanks a lot!!
0
Question by:ravichand-sql
• 5
• 2

LVL 18

Assisted Solution

lludden earned 333 total points
ID: 39241325
Find the next Friday after the current date (non-inclusive), then go back 3 weeks

``````SET DATEFIRST 1
DECLARE @DateToTest DATE = '20130614'
SELECT dateadd(day,-21,dateadd(day, CASE WHEN datepart(dw,@DateToTest) < 5 THEN 5 - datepart(dw, @DateToTest) ELSE 12 - datepart(dw,@DateToTest) END, @DateToTest))
``````
0

LVL 18

Assisted Solution

lludden earned 333 total points
ID: 39241352
Or for the default datefirst

``````SET DATEFIRST 7
DECLARE @DateToTest DATE = '20130612'
SELECT dateadd(day,-21,dateadd(day, CASE WHEN datepart(dw,@DateToTest) < 6 THEN 6 - datepart(dw, @DateToTest) ELSE 13 - datepart(dw,@DateToTest) END, @DateToTest))
``````
0

LVL 48

Expert Comment

ID: 39241455
this approach uses modulus 7 and is independent of the datefirst (or any other) dbms setting(s)
``````declare @dateFrom as datetime
set @dateFrom = getdate() -- normally this would be used
--set @dateFrom = '2013-06-12' -- test 1
--set @dateFrom = '2013-06-14' -- test 2

set @datefrom = case when datediff(day,0,@dateFrom) % 7 = 4 then dateadd(day,-14,@dateFrom)
end

select @dateFrom, datediff(day,0,@dateFrom) % 7

--test 1: May, 24 2013
--test 2: May, 31 2013
``````
0

LVL 48

Expert Comment

ID: 39241461
oh, 0 is always Monday using this modulus approach, 4 is always Friday
0

LVL 48

Accepted Solution

PortletPaul earned 167 total points
ID: 39243551
I checked my results just now and found it was in error. This was due to me wondering what you wanted if getdate() was Sat/Sun and here I assume you would want the same results as a getdate() of Friday. So a revised approach
tests:
--set @dateFrom = '2013-06-10' -- test Mon 0
--set @dateFrom = '2013-06-11' -- test Tue 1
--set @dateFrom = '2013-06-12' -- test Wed 2
--set @dateFrom = '2013-06-13' -- test Thu 3
--set @dateFrom = '2013-06-14' -- test Fri 4
--set @dateFrom = '2013-06-15' -- test Sat 5
--set @dateFrom = '2013-06-16' -- test Sun 6
results:
-- Monday            0      2013.05.24      Friday
-- Tuesday            1      2013.05.24      Friday
-- Wednesday      2      2013.05.24      Friday
-- Thursday         3      2013.05.24      Friday
-- Friday               4      2013.05.31      Friday
-- Saturday          5      2013.05.31      Friday
-- Sunday             6      2013.05.31      Friday
from this code:
``````declare @dateFrom as datetime
set @dateFrom = getdate() -- normally this would be used

set @datefrom = case when datediff(day,0,@dateFrom) % 7 >= 4 then dateadd(day,-10-(datediff(day,0,@dateFrom) % 7 ),@dateFrom)
end

select convert(varchar, @dateFrom ,102), datename(weekday,@datefrom)
``````
http://sqlfiddle.com/#!6/59e7c/1
0

LVL 48

Expert Comment

ID: 39250753
Happy you have a solution.

A should be the default grade awarded unless the answer is deficient.

0

Author Comment

ID: 39250769
Hi Paul,

Im sorry.. I did not know this. Please let me know if I can change the grade for this question. I will make sure I will grade appropriately from the next question.

Thanks!!!
0

LVL 48

Expert Comment

ID: 39250805
You may request attention for the question to be re-opened, and then amend.

Thanks for sharing out the points by the way - I believe that is a good thing to do when there are multiple solutions (but you are effectively also marking me down by awarding twice to variants of the same solution).
0

## Featured Post

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.