How generate Dates on the fly

Question

How generate Dates on the fly

NY153

Old Hand

Points: 333
More actions
March 4, 2009 at 9:21 pm

#128240

How generate Dates on the fly? At runtime for the last 5 years, current year -5 starting from 1st Jan.
I needed for the left JOIN, so I can get missing dates.
Mr. 25$ (Article authors) stay away.

Viewing 15 posts - 1 through 15 (of 55 total)

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

You could use a function to generate the dates for the range you need, or you could pre-populate a permanant table with the dates you need, and select the range you need.

The function on the link below can be used to generate the date range you need, or it can be used to populate a permanent table.

Date Table Function F_TABLE_DATE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

NY153 Old Hand Points: 333 More actions · Answer 2

I can't create anything on database, I don't have rights.

I guess my question was clear enough - at run time.

Mr. 25$ (Article authors) stay away.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You can look at the logic inside the Date Table Function F_TABLE_DATE to see how it does it, since it generates the dates at run time.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

NY153 (3/4/2009)
How generate Dates on the fly? At runtime for the last 5 years, current year -5 starting from 1st Jan.
I needed for the left JOIN, so I can get missing dates.

NY153 (3/4/2009)
I can't create anything on database, I don't have rights.
I guess my question was clear enough - at run time.

Sounds to me like they've pretty much hog tied ya. They don't have a Developer's database to work in?

Anyway, based on your request, the following will generate dates starting on the 1st of Jan 5 years ago, and generate every date up to the current date... and nothing gets created. 😉 Use it either as a CTE or a "Derived Table" to satisfy your table source for the left outer join.

SELECT TOP (DATEDIFF(dd, DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0), GETDATE()) + 1)

DATEADD(dd, (ROW_NUMBER() OVER (ORDER BY sc1.ID)-1), DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)) AS Date

FROM Master.sys.SysColumns sc1

CROSS JOIN Master.sys.SysColumns sc2

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

Dugi SSCoach Points: 17998 More actions · Answer 5

Yea, great simple code Jeff...very nice combination DATEDIFF and DATEADD functions to generate the results ...once again great simple-little code here!

:hehe::hehe::hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Thanks Dugi! Great way to start the day!

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

JEFF !!!! You don't have a tally table available?

select dateadd(yy,datediff(yy,0,getdate())-5,0)+ N-1

from dbo.tally

where N < datediff(dd,dateadd(yy,datediff(yy,0,getdate())-5,0),getdate())+2

Runs almost a full millisecond faster at 5-10 years. 😉

Pretty much a dead heat from two years in.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 8

Bob Hovious (3/5/2009)
JEFF !!!! You don't have a tally table available?
select dateadd(yy,datediff(yy,0,getdate())-5,0)+ N-1
from dbo.tally
where N < datediff(dd,dateadd(yy,datediff(yy,0,getdate())-5,0),getdate())+2
Runs almost a full millisecond faster at 5-10 years. 😉
Pretty much a dead heat from two years in.

Well, problem is that you need to have a tally table available...

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

NY153 Old Hand Points: 333 More actions · Answer 9

No systables as well 🙁

And tables available don't have seqeunce numbers. They some id columns, but not perfect.

Very very limited access

Mr. 25$ (Article authors) stay away.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

My bad.

I skimmed over the part that said nothing could be created in the DB. I was trying to poke fun at Jeff because he is usually Mr. Tally Table.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

Corrected version. Still runs in a millisecond or less.

;WITH

L0 AS (SELECT 1 AS C UNION ALL SELECT 1),--2 rows

L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows

L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows

L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows

L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows

tally AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)

select dateadd(yy,datediff(yy,0,getdate())-3,0)+ N-1

from tally

where N < datediff(dd,dateadd(yy,datediff(yy,0,getdate())-3,0),getdate())+2

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Bob Hovious (3/5/2009)
JEFF !!!! You don't have a tally table available?
select dateadd(yy,datediff(yy,0,getdate())-5,0)+ N-1
from dbo.tally
where N < datediff(dd,dateadd(yy,datediff(yy,0,getdate())-5,0),getdate())+2
Runs almost a full millisecond faster at 5-10 years. 😉
Pretty much a dead heat from two years in.

Heh... Yes, of course I do :hehe:... but read the requirements the op posted... can't build anything new like a Tally table.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

NY153 (3/5/2009)
No systables as well 🙁
And tables available don't have seqeunce numbers. They some id columns, but not perfect.
Very very limited access

The sys tables don't need to have any sequence numbers for the cross join code to work properly... ROW_NUMBER() is what actually makes the sequence of numbers.

Understood about no access to the sys tables, though... the post with the CTE in it that Bob Hovious made is how Itzek does it. I've tested it to a billion rows... it doesn't run as fast as the cross join for requirements below a million or so rows but it rocks for anything above that. It also rocks when you can't make a Tally table, can't reference any of the sys objects, and have generally been tied up with duct tape and sequestered to the nearest corner of the room. 😛

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Jeff Moden (3/5/2009)
Bob Hovious (3/5/2009)
JEFF !!!! You don't have a tally table available?
select dateadd(yy,datediff(yy,0,getdate())-5,0)+ N-1
from dbo.tally
where N < datediff(dd,dateadd(yy,datediff(yy,0,getdate())-5,0),getdate())+2
Runs almost a full millisecond faster at 5-10 years. 😉
Pretty much a dead heat from two years in.
Heh... Yes, of course I do :hehe:... but read the requirements the op posted... can't build anything new like a Tally table.

Bugger... sorry Bob, didn't see the other post...

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