Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I cannot recall where I got the data before so I'm asking here:

Where can I get or how can I generate standard date/time/calendar data?

The table would consist of columns such as:

main_date -- generate date mm/dd/yyyy
date_id -- yyyymmdd
day -- Monday, Tuesday
time -- hh:ss
time_id -- 120000 (or some int equivalent for each minute/second?)

and a few more columns maybe like the year, month, date (dd) - etc - which I cannot remember at the moment. Then there are, if I recall correctly, columns that cover timezone changes --

I'm not sure if the data was created by a script made by another DBA in my previous job, or if it is available on the internet - I can't seem to find it, but I am likely to believe it is somewhere out there. It could also have been a table already created and just ready for download (script?) -- The data I'm referring to doesn't change. Say, for example, 2013-06-30 will forever be a Sunday - nothing will change that. 12:00 PM will always be 12:00 PM. There are 24 hours each day - that's also constant.. I apologize if this is vague..

I'm really looking for a table that can be looked-up since the values per day per year will never change.

share|improve this question

closed as off-topic by Jon Seigel, RolandoMySQLDBA, Mat, Nick Chammas, Paul White Jul 1 '13 at 5:50

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

On the internet? In any book for data warehouse design? Too broad. How can one not remember the basics of a calendar / date table? Hire a consultant? – TomTom Jun 30 '13 at 20:56

You are in luck - I have exactly such a table here that I use to set up a SQL Server Analysis Services demo. This is for SQL Server 2008 R2.

Table definition

create table dbo.DimDate    
    DateKey date not null constraint DimDate_PK primary key clustered ,
    CalendarYear as datepart( yy, DateKey ) ,
    CalendarQuarter as datepart( qq, DateKey ) , 
    CalendarQuarterName as 'Q' + convert( char(1), datepart( qq, DateKey ) ) ,
    CalendarMonth as datepart( mm, DateKey ) ,
    CalendarMonthName as datename( mm, DateKey ) ,
    CalendarDay as datepart( dd, DateKey ) ,
    WeekdayNumber as datepart( dw, DateKey ) ,
    WeekdayName as datename( dw, DateKey ) ,
    -- First 4 months of year (Jan-Apr) are in previous financial year.
    FinancialYear as DATEPART( yy, dateadd( mm, -4, DateKey ) ) ,
    FinancialQuarter as DATEPART( qq, dateadd( mm, -4, DateKey ) ) ,
    FinancialQuarterName as 'Q' + convert( char(1), DATEPART( qq, dateadd( mm, -4, DateKey ) ) ) ,
) ;

Data entry

declare @Date date ;
set @Date = '2009-05-01' ;
while ( @Date < '2013-05-01' )
    insert into dbo.DimDate ( DateKey ) values ( @Date ) ;
    set @Date = DATEADD( dd, 1, @Date ) ;
end ;
share|improve this answer
I understand that the question has been put on hold -- but I'll go try your code anyway -- then I'll come back to try and modify the question - Thanks! – Yasker Yasker Jul 5 '13 at 2:02

There is no general table like this. You need to figure out the columns you need and then build the table yourself.

For that you can use DATEADD to add different intervals to a base date, which would be the lowest date that you need. The difference you could get from a tally table. The result would be a DATETIME value. That is the only column you need to store, the others can be calculated columns, even though you might want to persist them too.

For links search for "Date and Time Functions" in Books Online. "Tally Table" should give you a source of numbers for the intervals. The rest is up to your requirements.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.