• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

How to find a table rows between dates and one other side?

Consider the following data :
Table:Table1
Column:DateColumn
01/01/2010
10/01/2010
20/02/2010
04/03/2010
05/04/2010
06/05/2010

I would like to be able to select rows such that I can use where criteria with a start and end date where the row earlier or equal to the start date is returned through to the date which follows the end date.  The data will not necessarily be ordered so the selection needs to order the data too.  I am using sql server 2000 (not by choice!)
0
dgloveruk
Asked:
dgloveruk
1 Solution
 
Russell FoxDatabase DeveloperCommented:
SQL 2000 has a DateTime data type, but not DATE, so to compare dates you'll need to strip out the time factor or things will get messy. To strip off the time you can use this:
SELECT DATEADD(dd, DATEDIFF(dd, 0, @Date), 0)

Open in new window

So for your selection you can try the BETWEEN operator (some people don't trust it with dates), or you can use <= and >= operators. These assume @StartDate is older and @EndDate is more recent:
...WHERE MyDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) AND DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0)

Open in new window

Or:
...WHERE MyDate >= DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) 
AND MyDate <= DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0)

Open in new window

0
 
Brian CroweCommented:
Unfortunately, since you are using 2000 you won't be able to use LAG and LEAD functions introduced in 2012 or even a windowed ROW_NUMBER function from 2008.  For best performance you would need to insert the pertinent records into a temporary table with an IDENTITY column to provide reference and then self-join.

CREATE TABLE #Date
(
   ID INT IDENTITY(1,1),
   DateColumn DATETIME
);

INSERT INTO #Date (DateColumn)
SELECT DateColumn
FROM myTable
ORDER BY DateColumn;

SELECT CurrDate.DateColumn, PrevDate.DateColumn
FROM #Date AS CurrDate
LEFT OUTER JOIN #Date AS PrevDate
   ON CurrDate.ID = PrevDate.ID + 1;
0
 
dgloverukAuthor Commented:
Thank you Russell and Brian.
Brian was on track with what I was trying to ask... however I have come up with this idea but I am not sure if it has weaknesses or issues that you can see?

SELECT     *
FROM         (SELECT     TOP 1 Datecolumn
                       FROM          mytable
                       WHERE      (Datecolumn< @StartDate)
                       ORDER BY Datecolumn DESC) x
UNION
SELECT     Datecolumn
FROM        mytable
WHERE     Datecolumn BETWEEN @StartDate AND @EndDate
UNION
SELECT     TOP 1 Datecolumn
FROM         mytable
WHERE     Datecolumn > @EndDate
ORDER BY Datecolumn

Note I had to wrap the first select command since I was getting an error that seemed to revolve around having the first UNION after an order by?

Can you please advise if you think this approach has flaws?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Russell FoxDatabase DeveloperCommented:
Okay, so you want the one date before, the one date after, and everything in between? Sorry, I did misunderstand the question. The UNION operator does the ORDER BY at the end, after it pulls in all of the records, so you may need to wrap the final query, too, and then do a final ORDER BY at the end:
SELECT     *
FROM         (SELECT     TOP 1 Datecolumn
                       FROM          mytable
                       WHERE      (Datecolumn< @StartDate)
                       ORDER BY Datecolumn DESC) x
UNION
SELECT     Datecolumn
FROM        mytable
WHERE     Datecolumn BETWEEN @StartDate AND @EndDate
UNION
SELECT *
FROM          (SELECT TOP 1 Datecolumn
                       FROM   mytable
                       WHERE     Datecolumn > @EndDate
                       ORDER BY Datecolumn
                       )
ORDER BY DateColumn

Open in new window

0
 
Anthony PerkinsCommented:
If you are going to use UNION, then make sure you are using UNION ALL.
0
 
dgloverukAuthor Commented:
Thanks Russell for taking the time to improve my solution by pointing out the union function and possible date order pitfalls!
Regards,
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now