Using SET DATEFIRST 1 (Monday as day 1 )

Question

Using SET DATEFIRST 1 (Monday as day 1 )

pwalter83

SSChampion

Points: 14575
More actions
July 1, 2011 at 8:20 am

#243101

Hi,
I am working on a report which displays the week numbers for a particular year. The problem is the week number displayed is incorrect for e.g. week shows up as 23 when it is actually 22 for the date June 1, 2011. I am using datepart to extract the week which is displayed as 201125 (for a week in June 2011)
After searching through google, I found that SET DATEFIRST option can be used to correctly display the week number. However, even after including the SET DATEFIRST 1 (Monday as day 1 and sunday as day 7) in the SP, it still shows up wrong on the report.
Could someone pls tell how can this be implemented to display the week number correctly ?
Thanks and Regards,
Paul

Viewing 9 posts - 1 through 9 (of 9 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Assuming you're looking for the ISO week number the DATEFIRST approach won't help you at all. The ISO week number depends on the weekday of January 1st.

Since you're using SS2K5 (based on the forum you posted in), there are basically three options I can think of:

a) use a calendar table and store the week number per date,

b) use a scalar-valued function as described in BOL, section "CREATE FUNCTION", example "A"

c) change the scalar function from b) into a inline-table valued function for better performance

Starting with SS2K8 the ISO_week is a new parameter of the DATEPART() function. Makes live a lot easier, I admitt... 😉

I use option a). The column gets populated by using a SS2K8 machine 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pwalter83 SSChampion Points: 14575 More actions · Answer 2

LutzM (7/1/2011)
Assuming you're looking for the ISO week number the DATEFIRST approach won't help you at all. The ISO week number depends on the weekday of January 1st.
Since you're using SS2K5 (based on the forum you posted in), there are basically three options I can think of:
a) use a calendar table and store the week number per date,
b) use a scalar-valued function as described in BOL, section "CREATE FUNCTION", example "A"
c) change the scalar function from b) into a inline-table valued function for better performance
Starting with SS2K8 the ISO_week is a new parameter of the DATEPART() function. Makes live a lot easier, I admitt... 😉
I use option a). The column gets populated by using a SS2K8 machine 😀

Thanks, Lutz, I tried but could not work out how to use a calendar date and then storing the same. Do you know if there's another way to do this ?

Thanks,

Paul

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Download my calendar table

http://www.sqlservercentral.com/Forums/Attachment8839.aspx

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Ninja's_RGR'us (7/4/2011)
Download my calendar table
http://www.sqlservercentral.com/Forums/Attachment8839.aspx

Thanks...but I am not sure what to do with it...and besides its not creating a table and giving errors. This is quite new to me and so I am a bit confused. Also, would you know if I need to use this calendar to assign Monday as day 1 and Sunday as day 7 ?

Thanks,

Paul

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

What errors are you getting?

The whole point of that table is to give you the ability to join to it on a date value and fetch all other date parameters you can think of.

pwalter83 SSChampion Points: 14575 More actions · Answer 6

Ninja's_RGR'us (7/5/2011)
What errors are you getting?
The whole point of that table is to give you the ability to join to it on a date value and fetch all other date parameters you can think of.

Thanks. I am getting the below errors:

--------------------------------------------------

Msg 1921, Level 16, State 1, Line 1

Invalid filegroup 'Data Filegroup 1' specified.

Msg 208, Level 16, State 1, Line 1

Invalid object name 'dbo.Calendar'.

Processed 100 total records

Msg 208, Level 16, State 1, Line 2

Invalid object name 'dbo.Calendar'.

Processed 200 total records

Msg 208, Level 16, State 1, Line 2

Invalid object name 'dbo.Calendar'.

Processed 300 total records

---------------------------------------------------

am i doing something wrong ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

In the create table part there's a line that say with [data file group...]

change that to primary or just comment that part out, it'll go to your default datafile group.

Then the inserts should work.

LutzM SSC Guru Points: 107049 More actions · Answer 8

pwalter83 (7/4/2011)
...
Thanks, Lutz, I tried but could not work out how to use a calendar date and then storing the same. Do you know if there's another way to do this ?
Thanks,
Paul

Errmmm... :unsure:

In my previous post I mentioned not only the calendar table concept but also using a function (options (b) and (c) ) including a source where to find it.

In case you don't have BOL installed on your system, here's the online version. Just scroll down to example A.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]