Dates Stored Procedure

Question

Dates Stored Procedure

JonJon

Say Hey Kid

Points: 676
More actions
October 10, 2007 at 12:31 pm

#375613

Is there anywhere I can get a SP to create a dates table.
It needs to include
Day, Month, Year, Fiscal Day, Fiscal Month, Fiscal Year, Week Number and Day Name.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If you do a search of SSC, I am pretty sure you will find what you are looking for. I have seen several people either post scripts or links to scripts for what you are requesting.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

A quick search on Google also turns up a bunch of links...

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

This is a nice article on how to create your own permanant calendar table and probably you should be able to script your stored procedure based on this.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Prasad Bhogadi
www.inforaise.com

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

SSC

http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/

N 56°04'39.16"
E 12°55'05.25"

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

Heh... what'cha gonna use it for? Forget about using it for batch programming... better be RBAR GUI code only (RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") or it'll kill you for performance.

For example... you have a table with a StartDate and an EndDate... you want to know how many WeekDays are represented by those two dates for every row in the table. How do you think you're going to do that? Aggregate Join to the date table? Function that does lookups in the date table? All will be terribly slow for resolving the whole 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)

JonJon Say Hey Kid Points: 676 More actions · Answer 6

Thanks everyone for the code and examples, but the code around the web is incorrect.

On some years (2004, 2005,2009, 2010) it gives you 53 weeks in a year.

Dont know about anyone else but Ive never seen a calendar with 53 weeks.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 7

If you post the code you're using, we might be able to help.

In the meantime, you might want to check out this link and the referenced resources:

http://www.karaszi.com/SQLServer/info_datetime.asp

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

JonJon (10/10/2007)
Is there anywhere I can get a SP to create a dates table.
It needs to include
Day, Month, Year, Fiscal Day, Fiscal Month, Fiscal Year, Week Number and Day Name.

You need to provide your definition for Fiscal Day, Fiscal Month, Fiscal Year, and Week Number.

All of those are very organization specific, and we have no way of knowing what the rules are for your organization.

It is not uncommon to have a fiscal year with 53 weeks, especially in retail. You need to have some place to put the leftover days. 52*7 = 364, so what do you do with days 365 or 366 of the year?

This link provides code for a generic date table:

Date Table Function F_TABLE_DATE

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

Dont know about anyone else but Ive never seen a calendar with 53 weeks.

This is one of the 'fun' parts with dates and calendars. When you say 'anyone', in places like this, where nationality is global, 'anyone' is 'everyone'. 😉

It's pretty common to have 53 weeks here in Sweden. It comes around once in a while.

The thing to remember with weeknumbers, is that they are different depending on the calendar.

/Kenneth

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

..and that all leads to a question of definition. "Full week" vs partial, which weeks gets included in which year. That's all driven by what you are going to do with the data.

look at it this way - most calendar years only sport 51 full weeks (and 2 partial weeks).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?