Solved

How can I get the date column I need?

Posted on 2013-05-10
15
422 Views
Last Modified: 2013-05-13
current: (This works fine)
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE

Desired:
I also need to get date column which is within -30 days of date1

date2 is within date1 - 30days

So that date2 can be any date included in date1 -30 days range
0
Comment
Question by:rhservan
[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
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39156812
date2 will be dateadd(dd, -30, date1)
0
 

Author Comment

by:rhservan
ID: 39156868
Just to clarify:

1. date2 is already its own column as a date. It lists dates beyond the date1 -30 date range.

2. So I need to filter the date2 column based on date1 -30 day range.

so can you write the formula so that it includes the existing date2 column?  Or, How can I apply the formula you sent.
0
 

Author Comment

by:rhservan
ID: 39156888
I am looking to have date2 column return only the dates in the date range of date1 -30days.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39156909
I am not quiet understood. Is this what you are looking

WHERE date1 >= DATEADD(dd, -30, @STARTDATE )
and date1 <= @StartDate
0
 

Author Comment

by:rhservan
ID: 39157027
date1 >= 01/01/2013 --@STARTDATE
 date1 <= 02/28/2013 --@ENDDATE

columns:
quote#          date1               date2
     1           01/01/2013      12/21/2012    <----This date is within -30 days of date1
     2           02/17/2013      01/25/2013    <----This date is within -30 days of date1
     3           02/08/2013      11/03/2012    <----This date is not within -30 days of date1
                                                                           I do not want to return this row.


I only want to return a row if the date2 column is within -30 days of date1
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39157042
WHERE date2 <= Dateadd(dd, 30, Date1 )

if you want to add to the existing where clause use this

WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 <= Dateadd(dd, 30, Date1 )
0
 

Author Comment

by:rhservan
ID: 39157084
Okay, I think you are getting close.
There is still a problem - It is returning up to 9 months difference.

Example:
     date1                              date2
10/15/2012                    01/27/2012    <------this should be within the range of date1 -30
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39157098
you can put another condition there
and date2 <= Dateadd(dd, 30, Date1 )
and date1 < date2
0
 

Author Comment

by:rhservan
ID: 39157106
But date1 should never be less than date2
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39157109
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and datediff(dd, date1, date2 ) <= 30
0
 

Author Comment

by:rhservan
ID: 39157138
Can't seem to make that work either.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39157284
try this.
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 >= Dateadd(dd, -30, Date1 )

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39157624
I believe Sharath has identified an appropriate logic.

Do the Date1 or Date2 values also contain "time" or is "time" always at 00:00:00?
You may want to do this just in case:
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  Date1 ), 0) )

Open in new window

or you can use
cast(Date1 as date)

if you don't like this syntax:
dateadd(dd, datediff(dd,0,  Date1 ), 0)
0
 
LVL 32

Expert Comment

by:awking00
ID: 39158239
where dateadd(dd,30,date2) >= date1
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39158311
do you have it working? maybe you can try:

WHERE date1 BETWEEN @STARTDATE and @ENDDATE
and date2 BETWEEN Dateadd(dd, -30, Date1 ) and Date1
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display SQL maintenance plan SQL Code 3 45
MDT Create a bootable media for BIOS 2 23
efficient backup report for SQL Server 13 79
SQL State HYT00. Timeout expired proplem 8 42
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

737 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