Date Calendar

  • Comments posted to this topic are about the item Date Calendar

  • Thank you for the script, will be very useful.

    Tony

  • My pleasure. Hope it helps!

  • I'm getting syntax errors in SQL 2008 in the Northwind D-Base beginning with this line...:

    WITH cte_date_base_table AS

    Also it doesn't recognize ROW_NUMBER as a function name.

    Help!

    SVonTilbon

  • It was developed on SQL Server 2005, though it should work on 2008. Have you tried running it on a different database? Also, sometimes when you copy scripts from SQLServerCentral.com it also copies "funky" (and sometimes "invisible") characters with the code. It's happened to me and I've needed to reformat the code before it would run. Hope this helps.

    Isn't Northwind SQL 2000? You need at least 2005 for the code to work as ROW_NUMBER is not recognized in 2000 nor is CTE.

  • Nice-looking date dimension table. 🙂

  • Thanks! Dates can be a REAL pain to work with sometimes, so I figured that this could help a lot of people.

  • @SVonTilbon: works fine in sqlserver 2008 express

  • Thank you for sharing this, dates are kind of "fun" (or a pain). Now the script only lacks a parameter to set the first day in the week to Monday (= the case in Europe) and to add a column isoweek (isoweek 1= first week with at least 4 days in it I guess) - for example 1 January 2010 is in week 53 of the year 2009 ! so isoweek 200953 or something like that.

    Guess that is work for somebody else ! 😉

  • I'll put it on the to do list. 🙂 Great idea. As for the first day of the week, it should change automatically to whatever your SQL Server instance settings are. So if day 1 is Monday rather than Sunday, the code should compensate for this. Hope that helps.

  • Okay, now I saw the light 🙂 THX - (found more explanation on http://www.kodyaz.com/articles/sql-set-datefirst-scope-permanent-for-sql-server-database.aspx)

    * first day in week depends on language of the session

    * language of session depends on language of user (login)

    * language of user is in most cases default the default language of the instance but can be different

    But the language of the session has an implication on the format of the dates too (us english 12/31/2009 <=> french 31/12/2009)

    So first to make the script work for all logins with different languages I suggest to change the implicit conversion of the border-dates to an explicit one which will make it language-independent (if not and the language is french, the dates could be wrongly interpreted).

    SET @vDate_Start = CONVERT(datetime,'01/01/2010',101)

    SET @vDate_End = CONVERT(datetime,'12/31/2010',101)

    or if you like it the iso-way

    SET @vDate_Start = CONVERT(datetime,'20100101',112)

    SET @vDate_End = CONVERT(datetime,'20101231',112)

    Now if the language of the user really reflects his own language, all should work fine, but this is not always the case (all installed default => language of the users us_english in a European bi-lingual environment, etc.).

    Now to add to this script the possibility to change the first day of the week one can do 2 things at the beginning of the script:

    1) to change in a session the first day in the week

    SET DATEFIRST 1

    This will only change the first day to monday, all names of months and days will still be in English.

    2) or you can set the language of a session:

    SET LANGUAGE dutch

    This will also show the names of months and days in Dutch (and as well change the first day to monday)

    bye!

    Lucas

  • You're right! My problem was w/ Northwind (SQL version) not the script.

    Thanks 4 the feedback - and thanks for a handy routine!

  • Awesome, thank you! 😀

    [font="Courier New"]____________________________________________________________________________________________
    Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
    [/font]

  • NP. 🙂 LOVE the quote. 🙂

  • Hey Sean, we have a script that has to run the same code each week of the month, except the 2nd Monday week it has to run different code. So sometimes that might be the 3rd calendar week and sometimes the 2nd calendar week. Your month_day_name_instance solves that problem perfect!

    We are already thinking of other programs here that will be using this. If you can come up with a way to import Federal Holidays, then it would have everything we need. Thanks again, this is soooo helpful!

    [font="Courier New"]____________________________________________________________________________________________
    Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
    [/font]

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

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