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

Posted on 2014-08-29
Last Modified: 2014-08-30
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!)
Question by:dgloveruk
    LVL 13

    Expert Comment

    by:Russell Fox
    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

    LVL 34

    Expert Comment

    by:Brian Crowe
    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.

       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;

    Author Comment

    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?
    LVL 13

    Accepted Solution

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

    Open in new window

    LVL 75

    Expert Comment

    by:Anthony Perkins
    If you are going to use UNION, then make sure you are using UNION ALL.

    Author Closing Comment

    Thanks Russell for taking the time to improve my solution by pointing out the union function and possible date order pitfalls!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now