SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Calendar


Date Calendar

Author
Message
ldemeyer@telenet.be
ldemeyer@telenet.be
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 110
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
SVonTilbon
SVonTilbon
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 63
You're right! My problem was w/ Northwind (SQL version) not the script.

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



Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 865
Awesome, thank you! :-D

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
NP. Smile LOVE the quote. Smile
Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 865
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!

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
I am actually working on "holiday logic" for the next version. Smile
jose-1149476
jose-1149476
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
Gracias !!! Un muy buen trabajo...
Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
Gracias!
Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 865
Hey Sean, I still use this wonderful program at work! Last year I added a is_workday column to flag for holidays. And created a script to generate the holidays for me. I can email it to you if you want it. :-D

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

Sean Smith (SSC)
Sean Smith (SSC)
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 990
Yes, please do! Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search