Making Sunday DayofWeek = 1

Question

Post reply

Making Sunday DayofWeek = 1

GolfBoy

SSC Rookie

Points: 27
More actions
March 15, 2006 at 2:02 am

#112039

Is there anyway of setting the SQL server so that the DatePart(DW) always returns a 7 for a Sunday without having to use the SET DATEFIRST in every SP.

Only reason being that we have 1 SQL server Returning 7 for a Sunday and another Returning 1, so need to make the same.

Using SQL2000

Thanks
Ian

Always Think IT!

Viewing 4 posts - 1 through 3 (of 3 total)

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

kevin mann Hall of Fame Points: 3894 More actions · Answer 1

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_4qic.asp

Robert Jones-161741 SSC Enthusiast Points: 154 More actions · Answer 2

The first day of the week is controlled by the language specified of the user login.

Default Language - "English" returns a 7 while "British English" will return 1.

Jorge Antonio SSCrazy Points: 2435 More actions · Answer 3

Hello

You could try the following lines:

declare @day_number tinyint
      select @cda_today=datepart(weekday, getdate())
      if @cda_today<7
        select @day_number=7+(@cda_today-7))+1
      else
        select @day_number=(7-(@cda_today))+1

The code that is shown above can be contained as a a function os sp, so you will be able to reuse it.
Notice that @day_number will always return Sunday as the first dday of week in an environment where Default Language - "English" returns a 7 while "British English" will return 1.