Solved

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

Posted on 2013-06-12
9
497 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39241461
oh, 0 is always Monday using this modulus approach, 4 is always Friday
0
 
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 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