Not sure exactly how this is happening, maybe someone could explain it to me please?
I have a column called DateKey, which is an int data type. Dates are stored in the format YYYYMMDD, so today's date of 4th Feb 2021 is represented as 20210204.
I am converting this into datetime and trying to extract the week number (it's part of a larger function), but somehow I have ended up with 53 weeks in the last year and it shows me that for the current date 20210204 the week number is 6, when in fact it is week 5.
declare @DateKey int
Set @DateKey='20210204'
select (DATEPART(week, CONVERT(datetime, CAST(@DateKey AS CHAR(8)))))
Any ideas why this is happening?
It all depends on how you define the week. You may want to use iso_week or have a table with your own definitions:
SELECT DATEPART(iso_week, '20210204'), DATEPART(week, '20210204');
February 4, 2021 at 7:19 pm
It seems like you're doing a straightforward counting of 7 days as a week, regardless of the staring day of the week (SQL doesn't do that), then try this calc instead:
select ceiling(datepart(dayofyear, convert(date, cast(@DateKey as char(8)))) / 7.0) as week#
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 5, 2021 at 9:00 am
iso_week has worked wonderfully, thank you! I am going to use this in the future. Having iso-week, I don't see the point of week, to be honest.
February 5, 2021 at 10:18 am
ISO weeks start on a Monday so you might want to check this is okay.
February 5, 2021 at 4:08 pm
Actually, in ISO week, week# is controlled by Thursday being in the week. (Seriously, you can look it up.)
So, since this year happens to start on a Friday, ISO week and 7-day week will return the same result. But next year it will not.
Thus, you need to review your requirements and make sure which week you need.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 5, 2021 at 5:45 pm
You are correct. I was trying to make the point that an iso_week runs Monday to Sunday. If the OP wants something else, say Sunday to Saturday, then some sort of offset is required.
SELECT DATEPART(iso_week, '20210103') -- Sunday
,DATEPART(iso_week, '20210104'); -- Monday
February 5, 2021 at 6:01 pm
You are correct. I was trying to make the point that an iso_week runs Monday to Sunday. If the OP wants something else, say Sunday to Saturday, then some sort of offset is required.
SELECT DATEPART(iso_week, '20210103') -- Sunday
,DATEPART(iso_week, '20210104'); -- Monday
ISO week sets the week number in the previous/following years also - which means for year 2020 week 53 ends on 2021-01-03 and week 1 for 2021 starts on 2021-01-04. The end of 2021 is 2022-01-02 and 2021 only has 52 weeks where 2020 has 53 weeks.
The week number is based on how you determine the start of the year - for ISO the start of the year is the Monday prior to the first Thursday of the year. For US the first of the year is 01/01 always and you get a short week 1 (except when Sunday is the 1st of the year) and a short 52/53 week at the end of the year.
To count the number of weeks for a year - you need to define what the first of the year is for your calendar. That will be different for calendar year vs fiscal year vs retail, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 9, 2021 at 4:07 pm
If I try to use iso_week, I get the following 'amazing' results:
20201225 2020_52
20201228 2020_53
20201229 2020_53
20201230 2020_53
20201231 2020_53
20210101 2021_53
20210104 2021_1
20210105 2021_1
20210106 2021_1
20210107 2021_1
20210108 2021_1
20210111 2021_2
20210112 2021_2
20210113 2021_2
20210114 2021_2
20210115 2021_2
20210118 2021_3
20210119 2021_3
20210120 2021_3
20210121 2021_3
20210122 2021_3
20210125 2021_4
20210126 2021_4
20210127 2021_4
20210128 2021_4
20210129 2021_4
20210201 2021_5
20210202 2021_5
20210203 2021_5
20210204 2021_5
20210205 2021_5
20210208 2021_6
20210209 2021_6
So, I've decided to get back to using week, which treats week 1 of 2021 as a week with one working day only, like it should be and doesn't create 2021_53 as above. I have also created a CASE statement where 2020_53 is converted into 2020_52.
February 9, 2021 at 4:15 pm
I've decided to get back to using week, which treats week 1 of 2021 as a week with one working day only, like it should be
So are you stating that the ISO week definition is categorically wrong?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 9, 2021 at 4:17 pm
If you tell us what you consider to be a "week" -- as we asked for several times -- we could give you code specific to your needs.
I wouldn't rely on DATEPART(WEEK, ever, since it changes with the DATEFIRST setting. Since we have locations around the world, I don't like any code that returns inconsistent values depending on where it is run.
Iso_week is consistent, so it can be used if it really matches your need.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 9, 2021 at 4:44 pm
Monday to Sunday would be good for me to consider a week. It would be great to see the first week of 2021 as week 1 with just one working day (I don't really get data from Saturday and Sunday). Ideally, I wouldn't like to see week 53 at all.
The DATEPART(week...) I quoted in the original post is actually a part of a larger function, which looks like this:
RTRIM(CAST(DATEPART(year, CONVERT(datetime, CAST(DateKey AS CHAR(8)))) as char))+ '_' + RTRIM(CAST(DATEPART(week, CONVERT(datetime, CAST(DateKey AS CHAR(8)))) as char))
It extracts the year part and the week part out of a date and creates a week number like YYYY_WW.
February 9, 2021 at 5:35 pm
See if these calcs give you results more like what you want.
Edit: Note that the calcs are completely independent of any/all date and language and DATEFIRST setting and will work correctly under ANY of them.
/* create table of the sample dates you provided, adding another date to help verify date logic */
CREATE TABLE #dates (
date date NOT NULL,
yyyy_ww_old char(7) NULL
)
INSERT INTO #dates VALUES
('20201225','2020_52'), ('20201229','2020_53'), ('20201230','2020_53'), ('20201231','2020_53'),
('20210101','2021_53'), ('20210104','2021_1 '), ('20210105','2021_1 '), ('20210106','2021_1 '),
('20210107','2021_1 '), ('20210108','2021_1 '), ('20210111','2021_2 '), ('20210112','2021_2 '),
('20210113','2021_2 '), ('20210114','2021_2 '), ('20210115','2021_2 '), ('20210118','2021_3 '),
('20201228','2020_53'), ('20210119','2021_3 '), ('20210120','2021_3 '), ('20210121','2021_3 '),
('20210122','2021_3 '), ('20210125','2021_4 '), ('20210126','2021_4 '), ('20210127','2021_4 '),
('20210128','2021_4 '), ('20210129', '2021_4'), ('20210201', '2021_5'), ('20210202', '2021_5'),
('20210203', '2021_5'), ('20210204', '2021_5'), ('20210205', '2021_5'), ('20210208', '2021_6'),
('20210209', '2021_6')
INSERT INTO #dates VALUES
('20210103','')
/* show the new calcs needed to gen yyyy_ww */
SELECT
date, yyyy_ww_old,
CAST(YEAR(date) AS varchar(4)) + '_' +
CAST(DATEDIFF(DAY, first_Monday, date) / 7 + 1 AS varchar(2)) AS yyyy_ww_new
FROM #dates
CROSS APPLY (
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, date), 0) AS Jan_01,
DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, date), 0)) % 7 AS Jan_01_day
) AS calc1
CROSS APPLY (
SELECT CASE WHEN Jan_01_day = 0 /*Mon*/ THEN Jan_01
ELSE DATEADD(DAY, -DATEDIFF(DAY, 0, Jan_01) % 7, Jan_01) END AS first_Monday
) AS calc2
ORDER BY date
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 9, 2021 at 5:49 pm
Monday to Sunday would be good for me to consider a week. It would be great to see the first week of 2021 as week 1 with just one working day (I don't really get data from Saturday and Sunday). Ideally, I wouldn't like to see week 53 at all.
The DATEPART(week...) I quoted in the original post is actually a part of a larger function, which looks like this:
RTRIM(CAST(DATEPART(year, CONVERT(datetime, CAST(DateKey AS CHAR(8)))) as char))+ '_' + RTRIM(CAST(DATEPART(week, CONVERT(datetime, CAST(DateKey AS CHAR(8)))) as char))It extracts the year part and the week part out of a date and creates a week number like YYYY_WW.
It is not possible to never have a week 53...depending on when the "year" starts and ends will determine what years will have 52 weeks and what years will have 53 weeks.
To be able to define the week number - you have to be able to define the start of the year and how the week number will be defined. Do you always want 7 days in a week? Or - do you want week one to always start on 01/01 of any given year with week 1 a shortened week (note: week 53 here will also be a shortened week).
ISO week 1 is the first week of the year with at least 4 days in the year, which translates to the week with the first Thursday (Monday through Thursday is the minimum number of days for the first week). This provides a consistent week numbering system across years...but does not necessarily allow for like-to-like comparisons.
For like-to-like comparisons, businesses will utilize a 4-5-4 type calendar (a 52/53 week calendar) and restate the calendar as needed. For example - if the year 2020 has 53 weeks on the 4-5-4 calendar there will be 2 versions, one that can be used to compare with the previous year(s) using the first 52 weeks - and a second one to be used to compare with following years using the last 52 weeks (restated so that week 1 becomes week 0...).
So the question for you is: What are you using this week number for? How do you envision using it - and what are the business requirements?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 10, 2021 at 9:10 am
The script is for a dashboard. I can't see it having a lifespan longer than 2 years. As regards the number of weeks, for me the key phrase in your reply is 'and restate the calendar as needed'.
I was just trying to understand, how I was getting 53 weeks and why 20210101 became 2021_53, but I think it's becoming clearer now.
Thank you.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply