Solved

How can I get the date column I need?

Posted on 2013-05-10
15
423 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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
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 49

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

695 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