How can I get the date column I need?
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
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
Aneesh
date2 will be dateadd(dd, -30, date1)
ASKER
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.
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.
ASKER
I am looking to have date2 column return only the dates in the date range of date1 -30days.
I am not quiet understood. Is this what you are looking
WHERE date1 >= DATEADD(dd, -30, @STARTDATE )
and date1 <= @StartDate
WHERE date1 >= DATEADD(dd, -30, @STARTDATE )
and date1 <= @StartDate
ASKER
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
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
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 )
if you want to add to the existing where clause use this
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 <= Dateadd(dd, 30, Date1 )
ASKER
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
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
you can put another condition there
and date2 <= Dateadd(dd, 30, Date1 )
and date1 < date2
and date2 <= Dateadd(dd, 30, Date1 )
and date1 < date2
ASKER
But date1 should never be less than date2
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and datediff(dd, date1, date2 ) <= 30
and date1 <= @ENDDATE
and datediff(dd, date1, date2 ) <= 30
ASKER
Can't seem to make that work either.
try this.
WHERE date1 >= @STARTDATE
and date1 <= @ENDDATE
and date2 >= Dateadd(dd, -30, Date1 )ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
where dateadd(dd,30,date2) >= date1
do you have it working? maybe you can try:
WHERE date1 BETWEEN @STARTDATE and @ENDDATE
and date2 BETWEEN Dateadd(dd, -30, Date1 ) and Date1
WHERE date1 BETWEEN @STARTDATE and @ENDDATE
and date2 BETWEEN Dateadd(dd, -30, Date1 ) and Date1