Solved

How can I get the date column I need?

Posted on 2013-05-10
15
421 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
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 45
SQL Consolidate rows 3 24
SQL Add Parameter in Variable 4 21
Searching a barcode number within a string. 7 24
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

679 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