October 10, 2007 at 12:31 pm
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.
October 10, 2007 at 4:10 pm
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.
October 11, 2007 at 6:09 am
A quick search on Google also turns up a bunch of links...
October 11, 2007 at 6:17 am
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.
Prasad Bhogadi
www.inforaise.com
October 11, 2007 at 7:12 am
SSC
http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/
N 56°04'39.16"
E 12°55'05.25"
October 11, 2007 at 8:41 pm
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
Change is inevitable... Change for the better is not.
October 12, 2007 at 9:43 am
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.
October 12, 2007 at 3:46 pm
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:
October 12, 2007 at 4:05 pm
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
October 15, 2007 at 1:43 am
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
October 15, 2007 at 9:51 am
..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?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply