Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Date Calendar Expand / Collapse
Author
Message
Posted Sunday, November 1, 2009 9:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:01 AM
Points: 66, Visits: 105
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
Post #812116
Posted Monday, November 2, 2009 7:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:20 PM
Points: 6, Visits: 46
You're right! My problem was w/ Northwind (SQL version) not the script.

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



Post #812370
Posted Monday, November 9, 2009 12:24 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:32 PM
Points: 460, Visits: 785
Awesome, thank you!

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
Post #816038
Posted Monday, November 9, 2009 12:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 AM
Points: 371, Visits: 735
NP. :) LOVE the quote. :)
Post #816042
Posted Monday, November 9, 2009 3:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:32 PM
Points: 460, Visits: 785
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
Post #816190
Posted Tuesday, November 10, 2009 6:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 AM
Points: 371, Visits: 735
I am actually working on "holiday logic" for the next version. :)
Post #816431
Posted Thursday, June 3, 2010 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 7:09 AM
Points: 1, Visits: 14
Gracias !!! Un muy buen trabajo...
Post #932234
Posted Thursday, June 3, 2010 9:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 AM
Points: 371, Visits: 735
Gracias!
Post #932242
Posted Thursday, June 3, 2010 9:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:32 PM
Points: 460, Visits: 785
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.

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
Post #932248
Posted Thursday, June 3, 2010 9:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 AM
Points: 371, Visits: 735
Yes, please do! :)
Post #932252
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse