Solved

How can I get the date column I need?

Posted on 2013-05-10
15
419 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 40

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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

803 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