Post reply

Auto Generate day table & week table

  • February 8, 2007 at 4:53 pm

    #171344

    Can anyone help, me with the following. I am trying to to generate two tables, with data for the current year.

    The day table :

    ID|DayDate

    And the week table:

    ID|weekNo|WeekStartDate|WeekEndDate.

    I am trying to populate the tables with all data up until the end of the year

    can anyone give me any advice how to generate these records.

    I'm assuming I can do it with a stored procedure, but am unsure on the scripting required.

    Many Thanks.

    Mark

  • February 8, 2007 at 7:23 pm

    #688720

    You can use this function to generate both.

    Date Table Function F_TABLE_DATE

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

    This code generates a table with all days from 2000-01-01 through 2010-12-31.  You can pick any date range you want by changing the start and end date.

    select
*
from
dbo.F_TABLE_DATE ( '20000101','20101231' )
order by
DATE

    Can you provide a little background on your definition of week?  When does the first week of the year start?  What day of the week does your week start on?  Does your week follow the ISO week rules?  I can probably follow up with a query to show you how to generate the week table if you give me a good definition of your organizations week.

     

     

Viewing 2 posts - 1 through 1 (of 1 total)

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