Solved

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

Posted on 2013-06-12
9
489 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 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

17 Experts available now in Live!

Get 1:1 Help Now