Date Calendar

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sean Smith » Date Calendar

41 posts, Page 2 of 5

««1 2 3 4 5 »»»

Date Calendar

Rate Topic

Display Mode

Topic Options

Author

Message

lukas-311170

Posted Sunday, November 01, 2009 9:26 AM

Valued Member

Group: General Forum Members
Last Login: Monday, December 10, 2012 12:11 PM
Points: 66, Visits: 101

Okay, now I saw the light Smile

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

SVonTilbon

Posted Monday, November 02, 2009 7:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 8:58 AM
Points: 5, Visits: 27

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

Thordog

Posted Monday, November 09, 2009 12:24 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:53 PM
Points: 381, Visits: 670

Awesome, thank you! BigGrin

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

Post #816038

Sean Smith-776614

Posted Monday, November 09, 2009 12:26 PM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:19 PM
Points: 90, Visits: 369

NP. :) LOVE the quote. :)

Post #816042

Thordog

Posted Monday, November 09, 2009 3:22 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:53 PM
Points: 381, Visits: 670

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

Sean Smith-776614

Posted Tuesday, November 10, 2009 6:17 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:19 PM
Points: 90, Visits: 369

I am actually working on "holiday logic" for the next version. :)

Post #816431

jose-1149476

Posted Thursday, June 03, 2010 9:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 04, 2013 12:53 PM
Points: 1, Visits: 13

Gracias !!! Un muy buen trabajo...

Post #932234

Sean Smith-776614

Posted Thursday, June 03, 2010 9:40 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:19 PM
Points: 90, Visits: 369

Gracias!

Post #932242

Thordog

Posted Thursday, June 03, 2010 9:46 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:53 PM
Points: 381, Visits: 670

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. BigGrin

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

Post #932248

Sean Smith-776614

Posted Thursday, June 03, 2010 9:49 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:19 PM
Points: 90, Visits: 369

Yes, please do! :)

Post #932252

« Prev Topic | Next Topic »

41 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions