Post reply

Date Range in Ms sql

  • December 20, 2011 at 10:20 pm

    #253911

    How can I display a result with given date range.

    I need a syntax for it.

    Thanks

    :crazy:

  • December 20, 2011 at 10:47 pm

    #1424000

    Example:

    USE AdventureWorks2008R2;

    GO

    SELECT e.FirstName, e.LastName, ep.Rate

    FROM HumanResources.vEmployee e

    JOIN HumanResources.EmployeePayHistory ep

    ON e.BusinessEntityID = ep.BusinessEntityID

    WHERE ep.Rate > 27 AND ep.Rate < 30 -- Date columns here

    ORDER BY ep.Rate;

    GO

    For More: http://msdn.microsoft.com/en-us/library/ms187922.aspx

  • December 20, 2011 at 10:53 pm

    #1424003

    Dev (12/20/2011)

    For More: http://msdn.microsoft.com/en-us/library/ms187922.aspx

    Oh, do be careful here. BETWEEN can lead to some really incorrect answers if dates with times are present in the data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 20, 2011 at 10:57 pm

    #1424006

    margieleneespanol (12/20/2011)

    How can I display a result with given date range.

    I need a syntax for it.

    Thanks

    :crazy:

    Provided the start and end of the date range are both expressed in "whole" dates...

    SELECT selectlist

    FROM dbo.sometable

    WHERE somecolumn >= @StartDate

    AND somecolumn < DATEADD(dd,1,@EndDate)

    Don't get into the habit of using BETWEEN for dates. I guarantee that it'll bite you some time in the future.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 20, 2011 at 11:00 pm

    #1424008

    Don't get into the habit of using BETWEEN for dates. I guarantee that it'll bite you some time in the future.

    Agree. My initial example uses < / > operators only. I suggested BETWEEN as an alternative (just FYI).

  • January 5, 2012 at 8:42 pm

    #1429439

    margieleneespanol (12/20/2011)

    How can I display a result with given date range.

    I need a syntax for it.

    Thanks

    :crazy:

    Are you all set now or what??? :blink:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply