Solved

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

Posted on 2013-06-12
9
486 Views
Last Modified: 2013-06-16
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.

Please help!!!

Thanks a lot!!
0
Comment
Question by:ravichand-sql
  • 5
  • 2
9 Comments
 
LVL 18

Assisted Solution

by:lludden
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))

Open in new window

0
 
LVL 18

Assisted Solution

by:lludden
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))

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
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)
                     else dateadd(day,-21+(datediff(day,0,@dateFrom) % 7 ),@dateFrom) 
                end

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

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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39241461
oh, 0 is always Monday using this modulus approach, 4 is always Friday
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 48

Accepted Solution

by:
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)
                     else dateadd(day,-17-(datediff(day,0,@dateFrom) % 7 ),@dateFrom) 
                end

select convert(varchar, @dateFrom ,102), datename(weekday,@datefrom)

Open in new window

http://sqlfiddle.com/#!6/59e7c/1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39250753
Happy you have a solution.

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

How was the answer deficient?
0
 

Author Comment

by:ravichand-sql
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

by:PortletPaul
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now