Print Dates between Min & Max Dates

Question

Post reply

Print Dates between Min & Max Dates

Galahad Phillips

Mr or Mrs. 500

Points: 551
More actions
February 24, 2006 at 9:25 am

#152779

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.

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Maybe:

SELECT MyDateField

FROM MyTableName

WHERE MyDateField > (SELECT MIN(MyDateField)

FROM MyTableName)

AND MyDateField < (SELECT MAX(MyDateField)

FROM MyTableName)

-SQLBill

JeffB Hall of Fame Points: 3541 More actions · Answer 3

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?

JeffB Hall of Fame Points: 3541 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

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)

JeffB Hall of Fame Points: 3541 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

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)

Galahad Phillips Mr or Mrs. 500 Points: 551 More actions · Answer 8

ALL:

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

Thanks again.

Galahad