Print Dates between Min & Max Dates

  • I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min & Max Dates

    Example.

    Min Date: 1/1/2005

    Max Date: 5/1/2005

    Dates between would be 2/1/2005,3/1/2005,4/1/2005

    Please advise and thanks for your help.

     

  • Create a date table that has all the dates you need.

    then query the table.

    Create table t_Dates (id int identity PRIMARY KEY CLUSTERED,

                                   [Date] smalldatetime NOT NULL Default('01/01/2005'))

    SELECT TOP 8000 number = IDENTITY(int, 1, 1)

    INTO   t_Numbers

    FROM   sysobjects t1, sysobjects t2, sysobjects t3

    insert into t_Dates (time_Date)

    select top 2040 dateadd(dd,number,'05/31/2004')

    from t_Numbers

    Drop t_Numbers

    EDIT:

    Add other info to date table like day of week etc, and you can query dates a mirad of ways

    [Day_of_Week] varchar(20) NOT NULL default(''),

    [Day_of_Month] tinyint NOT NULL  default(1),

    [Day_of_Year] smallint NOT NULL  default(1),

    [Month] varchar(12) NOT NULL  default(''),

    [Quarter] tinyint NOT NULL  default(1),

    [Year] smallint NOT NULL  default(2004),

    [Weekend] bit NOT NULL  default(0),

    update t_Dates

    set Day_of_Week = datename(dw,[Date]),

    Day_of_Month = datepart(dd,[Date]),

    Day_of_Year = datepart(dy,[Date]),

    Month = datename(mm,[Date]),

    Quarter = datepart(qq,[Date]),

    Year = datepart(yy,[Date]),

    Weekend = case when datepart(dw,[Date]) in (1,7) then 1 else 0 end

  • Maybe:

    SELECT MyDateField

    FROM MyTableName

    WHERE MyDateField > (SELECT MIN(MyDateField)

    FROM MyTableName)

    AND MyDateField < (SELECT MAX(MyDateField)

    FROM MyTableName)

    -SQLBill

  • Won't this just return all dates between the two earliest and latest rows in the database, including dupclicates?

    This would work better:

    SELECT DISTINCT MyDateField

    FROM MyTableName

    WHERE MyDateField > @MinDateField

    AND MyDateField < @MaxDateField

    But, there are still problems in that this will return all dates, not just the 1st days of the months?  What about dates for which no data exists?

  • I may have misread you problem and realized that you are using European style dates and what you really want is the dates between 01 Jan 2005 and 05 Jan 2005.

    This may work for you:

    declare @Seq Table(ID int)

    insert into @Seq

    select top 8001 0

    from master.dbo.syscomments a

    cross join master.dbo.syscomments b

    declare @i int

    set @i = -1

    update @Seq

      set @i = ID = @i + 1

    --Execute a query against the table with 2 dates

    declare @StartDate datetime

    declare @EndDate datetime

    select @StartDate = '01 Jan 2005',

           @EndDate = '05 Jan 2005'

    select dateadd(dd, ID, @StartDate)

    from @seq

    where ID > 0 and dateadd(dd, ID, @StartDate) < @EndDate

  • Howdy Jeff,

    You may want to consider using a temp table instead of a table variable here... table variables don't use statistics so they're inherently slower... both temp tables and table variables start off in memory and move to TempDB on disk if they get too large for memory so I see no particular advantage to using a table variable.

    On the other hand, not only because temp tables use statistics, I can see a lot of advantages to using a temp table instead of a table variable here.  For example, this does the same thing as your table variable with the advantages of not using a loop, etc....

     SELECT TOP 8001

            IDENTITY(INT,1,1) AS ID

       INTO #Seq

       FROM Master.dbo.SysComments sc1,

            Master.dbo.SysComments sc2

    In fact, THAT's the basis for a "Tally" or "Numbers" table and I recommend that everyone make a permanent one...

    --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)

  • Personally, I always use a permanent copy of the "Numbers" table too.  It is one of the first things that I install on each SQL server.  I was just including a memory table so that the my example would work.

  • I had a feeling you were one of the smart ones.    Thanks for the feedback.

    --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)

  • ALL:

    Thanks alot for the advice. The method of creating and querying a table worked best.

    Thanks again.

    Galahad

Viewing 9 posts - 1 through 8 (of 8 total)

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