SQL Server Performance

T-SQL Date Range Query

Discussion in 'Getting Started' started by Runners, Jan 30, 2008.

  1. Runners New Member

    Afternoon All,
    I'm after a bit of help on a query i'm trying to write in T-SQL and if i can help it, by avoiding creating functions in the database as i'm using 3rd party software.
    I have a StartDate and an EndDate, and I need to show the StartDate, the EndDate and all the dates in between those two.
    So i would get..... 24/12/07 <----start Date
    25/12/07
    26/12/07
    27/15/07 <-----End Date
    If anyone can help at all, that would be great,
    Thanks,
    Runners
  2. Adriaan New Member

    WHERE date_column BETWEEN date1 AND date2
    This includes both dates. It is important to format the dates correctly, because a literal date in T-SQL must be written as either 'mm/dd/yyyy' or 'yyyy-mm-dd'.
    There's an excellent discussion by Frank Kalis on the use of dates in T-SQL, available from the articles section of this site.
  3. Runners New Member

    Thanks for that Adriaan, I've got the where clause sorted, but it's the output i'm having trouble with. In a table, i have a start date and an end data, and i'm trying to manipulate that data so it outputs both of those dates and all of the dates in between those two dates using T-SQL.
  4. Adriaan New Member

    DECLARE @startdt DATETIME, @enddt DATETIME

    SET @startdt = '2007-12-24'
    SET @enddt = '2007-12-27'
    DECLARE @dates TABLE (MyDate DATETIME)

    INSERT INTO @dates VALUES (@startdt)
    WHILE @startdt < @enddt
    BEGIN
    SET @startdt = @startdt + 1
    INSERT INTO @dates VALUES (@startdt)
    END

    SELECT * FROM @dates
  5. Madhivanan Moderator

    [quote user="Runners"]
    Thanks for that Adriaan, I've got the where clause sorted, but it's the output i'm having trouble with. In a table, i have a start date and an end data, and i'm trying to manipulate that data so it outputs both of those dates and all of the dates in between those two dates using T-SQL.
    [/quote]
    For small number of date ranges

    DECLARE @startdt DATETIME, @enddt DATETIME
    SET @startdt = '2007-12-24'
    SET @enddt = '2007-12-27'
    DECLARE @dates TABLE (MyDate DATETIME)
    INSERT INTO @dates
    Select dateadd(day,number,@startdt) from master..spt_values
    where type='p' and dateadd(day,number,@startdt)<=@enddt
    SELECT * FROM @dates
  6. ScottPletcher New Member

    Key things to remember are:
    1) Datetime datatypes includes time.
    2) Default time is 00:00 (midnight).
    So:
    WHERE dateInTable >= CONVERT(CHAR(8), @startDate, 112)
    AND dateInTable < DATEADD(DAY, 1, CONVERT(CHAR(8), @endDate, 112))
    So that the entire starting day gets selected and the entire ending day (by specifying less than midnight of the next day).
    For example, if starting date is '24/12/2007' and ending date is '27/12/2007', then the results would be:
    ... >= '20071224' /*with time forced to start of day, midnight, or 00:00:00*/
    ... < '20071228' /*with time forced to midnight*/
  7. Madhivanan Moderator

    [quote user="ScottPletcher"]
    Key things to remember are:
    1) Datetime datatypes includes time.
    2) Default time is 00:00 (midnight).
    So:
    WHERE dateInTable >= CONVERT(CHAR(8), @startDate, 112)
    AND dateInTable < DATEADD(DAY, 1, CONVERT(CHAR(8), @endDate, 112))
    So that the entire starting day gets selected and the entire ending day (by specifying less than midnight of the next day).
    For example, if starting date is '24/12/2007' and ending date is '27/12/2007', then the results would be:
    ... >= '20071224' /*with time forced to start of day, midnight, or 00:00:00*/
    ... < '20071228' /*with time forced to midnight*/
    [/quote]
    Dont convert dates into CHARs
    The effecient method is
    where datecol>=dateadd(day,datediff(day,0,@start_date),0) and
    datecol<dateadd(day,datediff(day,0,@end_date)+1,0)

Share This Page