Post reply

Date Calendar

  • October 9, 2009 at 6:20 am

    #215287

    Comments posted to this topic are about the item Date Calendar

  • October 29, 2009 at 6:21 am

    #1072374

    Thank you for the script, will be very useful.

    Tony

  • October 29, 2009 at 6:25 am

    #1072377

    My pleasure. Hope it helps!

  • October 29, 2009 at 6:38 am

    #1072386

    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

  • October 29, 2009 at 6:45 am

    #1072395

    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.

  • October 29, 2009 at 10:54 am

    #1072639

    Nice-looking date dimension table. 🙂

  • October 29, 2009 at 11:02 am

    #1072649

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

  • October 29, 2009 at 6:16 pm

    #1072909

    @SVonTilbon: works fine in sqlserver 2008 express

  • October 29, 2009 at 6:35 pm

    #1072912

    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 ! 😉

  • October 30, 2009 at 6:29 am

    #1073059

    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.

  • November 1, 2009 at 9:26 am

    #1073593

    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

  • November 2, 2009 at 7:42 am

    #1073808

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

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

  • November 9, 2009 at 12:24 pm

    #1076855

    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]

  • November 9, 2009 at 12:26 pm

    #1076858

    NP. 🙂 LOVE the quote. 🙂

  • November 9, 2009 at 3:22 pm

    #1076984

    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 71 total)

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