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

Consider the following data :

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!)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

Open in new window

Brian CroweDatabase AdministratorCommented:
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.

   DateColumn DATETIME

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

SELECT CurrDate.DateColumn, PrevDate.DateColumn
FROM #Date AS CurrDate
   ON CurrDate.ID = PrevDate.ID + 1;
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
SELECT     Datecolumn
FROM        mytable
WHERE     Datecolumn BETWEEN @StartDate AND @EndDate
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
SELECT     Datecolumn
FROM        mytable
WHERE     Datecolumn BETWEEN @StartDate AND @EndDate
FROM          (SELECT TOP 1 Datecolumn
                       FROM   mytable
                       WHERE     Datecolumn > @EndDate
                       ORDER BY Datecolumn
ORDER BY DateColumn

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
If you are going to use UNION, then make sure you are using UNION ALL.
dgloverukAuthor Commented:
Thanks Russell for taking the time to improve my solution by pointing out the union function and possible date order pitfalls!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.