Extracting correct week from getdate()
-
Hi
Just found that datepart(week, getdate()) results in week = 7, while we actually are in week 6.
I assume this is related to the fact that our real calender says that week 1 starts in last years week 53 and ends january 9. Week one last for 9 days this year. But since the server see that january 1 and jan 2 is on saturday/sunday it places that as week 1 and january 3 as start of week 2. Thus the server adds one more than it should have done.
I do not want to alter all my week statments by substracting one on each of them. In my mind that would be an ugly way to do it, and I would have to undo it january next year.
Any workaround?
Regards
DJ
-
Actually, you are a tiny bit off. Week two starts on Sunday, January 2, 2005.
SELECT DATEPART( week, '01/01/2005')
SELECT DATEPART( week, '01/02/2005')
SELECT DATEPART( week, '01/03/2005')
Either way, this is rather goofy as one day does not constitute a week...
I wasn't born stupid - I had to study.
-
This is a known Sql Server issue - it doesn't implement an ISO week (i.e. week 1 = 1st week with at least 4 days in it).
BOL provides an example user defined function to return an ISO week - search BOL on the topics "ISO Week" or "Create Function" and you'll find the sample code.
If a UDF is not acceptable, then you could resort to joining to a table that maps actual dates to the week numbers as required by your business. However, you'll need to write the code to populate this table.
-
But...
By the same token, 8 days does not constitute a week. Its 1.132857 weeks. What we're talking about here is a business rule. You have to decide whether your week 1 starts the Sunday previous to 1/1, the Monday previous to 1/1, the Sunday after 1/1, always 1/1, ...
Obviously, Microsoft chose the Sunday previous, and you want the Monday after. I'm not sure if thats configurable in Microsoft, but you COULD code for it. Get the current year from getdate(), check the DAY for 1/1/"current year", and then adjust the week according to your business rule. This could be done in a function that you would call instead of using DATENAME.
Steve
PW beat me to it!
-
Look at a calendar for January. The weeks are numbered based on the rows that appear there. Week 1 is Jan 1 only. Week 2 is Jan 2 thru Jan 8.
If you want to count weeks such that Jan 1 thru Jan 7 is week 1, and Jan 8 - Jan 14 is week 2, etc, use code like this:
DECLARE @dt datetime
SET @dt = '1/1/2005'
WHILE @dt <= '1/31/2005'
BEGIN
SELECT @dt, ((datepart(dy, @dt)-1) / 7) + 1 AS WeekNumber
END
You could put the code into a UDF, like this:
CREATE FUNCTION dbo.fWeekNumber
(
@dt datetime
)
RETURNS int
AS
BEGIN
RETURN ((datepart(dy, @dt)-1) / 7) + 1
END
Then, just do this:
DECLARE @dt datetime
SET @dt = '1/1/2005'
WHILE @dt <= '1/31/2005'
BEGIN
SELECT @dt, dbo.fWeekNumber(@dt) as weeknumber
END
-
mkeast:
>Look at a calendar for January. The weeks are numbered based on the rows that appear there. Week 1
>is Jan 1 only. Week 2 is Jan 2 thru Jan 8.
Pardon me for asking, but what happened to 27-31 december 2004??? Is that also Week 1?
Sounds strange for a 4-day week fella.
Found this in Sql Server Book Online
"When using the ISO 8601 standard, week values are always from 1 through 53, as the first week of the year is guaranteed to have a minimum of 4 days.When using the ISO 8601 standard, week values are always from 1 through 53, as the first week of the year is guaranteed to have a minimum of 4 days."
YES, I would like to use the ISO 8601-standard, but HOW?
/olm ~ grrrumpy.
-
YES, I would like to use the ISO 8601-standard, but HOW?
This might be a start: http://www.google.com/search?q=ISO%208601
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
OLM,
No, Dec 26-Dec 31 are not week 1. Those are actually week 53 of the year 2004, according to SQL Server. A SQL Server week number is based on a Sunday thru Saturday grouping. Jan 1 starts week one, which ends with the first Saturday of a particular year. In 2005, that happens to be Jan 1 as well. So the SQL Server definition of week 1 for 2005 is just one day. Also, since 52*7 = 364, there will always be 53 weeks in a year.
If you don't like SQL Server definition of a week, define your own. As PW mentioned, create a UDF that implements your business rules.
I personally find SQL Server's definition of the week number useful. As I mentioned before, it matches the physical calendar layout, as well as the numbering of each "work week" with a given year.
Since I also deal with Fiscal years (for example, 10/01/2004 - 9/30/2005 is FY 2005), I have UDFs that return the month or week within the Fiscal year.
Mike
-
Hi Mike.
I am not sure I understand this one:
"I personally find SQL Server's definition of the week number useful. As I mentioned before, it matches the physical calendar layout, as well as the numbering of each "work week" with a given year."
Looking at my calendar this statement should result in week 1
datepart
(week, cast('20050103' as datetime))
but the result I get is 2.
Thus it does not match my calendar.
Best Regards
Dan
-
Hmm....
On my calendar, the first row in January contains Saturday, Jan 1, 2005. That is week 1, where a week is defined as Sunday thru Saturday AND each day of that week is in the current year.
The second row is week 2, and starts with Jan 2, 2005. Monday, Jan 3, 2005 is also in week 2, which ends with Saturday January 8.
There are several definitions of a "week". The SQL Server designers picked one based on the calendar layout. The ISO week that has also been discussed here requires that week one contain the first Thursday of the year. Also, ISO defines a week as Monday thru Sunday. Thus, the ISO week 1 for 2005 is Jan 3 thru Jan 9. For the business rules that exist in my work environment, that definition is not useful. It may be for you.
All I'm saying is that the SQL Server week numbering scheme works for me. I'm not saying it is any better than the ISO scheme.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply