[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-06-12
9
Medium Priority
?
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
9 Comments
 
LVL 18

Assisted Solution

by:lludden
lludden earned 1332 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 1332 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 49

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39241461
oh, 0 is always Monday using this modulus approach, 4 is always Friday
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 668 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 49

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 49

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

656 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