November 25, 2015 at 7:13 am
Hi Guys
I have been creating a report that uses week numbers and noted that 01-01-2016 is coming up as week 1 of 2016
but when I checked a calendar online that week is defined as week 53 of 2015
I quick check on MSDN brought up the following:
January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
Is there a way to get around this as my customer wants the first week of Jan 2016 to start on the 4th?
Any help will be appreciated.
Marc
November 25, 2015 at 7:22 am
Don't worry I forgot about ISO_WEEK
November 25, 2015 at 7:22 am
marc.heidemann (11/25/2015)
Hi GuysI have been creating a report that uses week numbers and noted that 01-01-2016 is coming up as week 1 of 2016
but when I checked a calendar online that week is defined as week 53 of 2015
I quick check on MSDN brought up the following:
January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
Is there a way to get around this as my customer wants the first week of Jan 2016 to start on the 4th?
Any help will be appreciated.
Marc
You can't change the behavior of DATEPART but you can do a number of things to make that work the way you want it to. Most of the time the best option is a calendar table. It provides mountains of flexibility and it what I would do for something like you are describing. http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2015 at 12:17 am
Refer below link. I think this post has exactly what you are looking for.
http://www.itdeveloperzone.com/2015/10/generating-week-dates-for-year-in-sql.html
November 30, 2015 at 7:23 am
sandeepmittal11 (11/26/2015)
Refer below link. I think this post has exactly what you are looking for.http://www.itdeveloperzone.com/2015/10/generating-week-dates-for-year-in-sql.html
Gosh that will work but the performance of that is going to be just painful. There is no need to use a loop for this type of thing. Even worse is a multi statement table valued function. A tally table would be a MUCH better approach then looping like this. The bigger issue as I see it is that the linked post doesn't really solve the OP's concerns.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply