Datefirst format
-
Hi All,
I have the below query , have set datefirst as Sunday (7), but here i am getting week 52 for Sunday and Week 53 for Monday.
I am suppose to get Week 53 for Sunday. Using ISO_Week since it matches closely except weekday.
set datefirst 7
SELECT DATEPART(ISO_WEEK, '2020-12-27 00:00:00.000'),Datename(WEEKDAY,'2020-12-27 00:00:00.000');
SELECT DATEPART(ISO_WEEK, '2020-12-28 00:00:00.000'),Datename(WEEKDAY,'2020-12-28 00:00:00.000');
Same here also need to get week 1 starting from 2nd-Jan-2022, but getting week 1 starting from 3rd-Jan-2022.
SELECT DATEPART(ISO_WEEK, '2022-1-2 00:00:00.000'),Datename(WEEKDAY,'2022-1-2 00:00:00.000');
SELECT DATEPART(ISO_WEEK, '2022-1-03 00:00:00.000'),Datename(WEEKDAY,'2022-1-03 00:00:00.000');
How to achieve this?
Thanks!
- LearnSQL wrote:
Hi All,
I have the below query , have set datefirst as Sunday (7), but here i am getting week 52 for Sunday and Week 53 for Monday. I am suppose to get Week 53 for Sunday. Using ISO_Week since it matches closely except weekday.
set datefirst 7 SELECT DATEPART(ISO_WEEK, '2020-12-27 00:00:00.000'),Datename(WEEKDAY,'2020-12-27 00:00:00.000'); SELECT DATEPART(ISO_WEEK, '2020-12-28 00:00:00.000'),Datename(WEEKDAY,'2020-12-28 00:00:00.000');
Same here also need to get week 1 starting from 2nd-Jan-2022, but getting week 1 starting from 3rd-Jan-2022.
SELECT DATEPART(ISO_WEEK, '2022-1-2 00:00:00.000'),Datename(WEEKDAY,'2022-1-2 00:00:00.000'); SELECT DATEPART(ISO_WEEK, '2022-1-03 00:00:00.000'),Datename(WEEKDAY,'2022-1-03 00:00:00.000');
How to achieve this?
Thanks!
ISO Weeks pay no heed to DATEFIRST. ISO Weeks ALWAYS start on a Monday no matter what. They can and will sometimes bleed week 1 to the last Monday of the the previous year and the last week of the previous year can bleed into the next year. It's the way ISO Weeks work. Period.
If you want weeks to ALWAYS be started on Sundays, what do you want to do with the "edge" cases of when a week straddles two years?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
How to get the missing weeks!
Emp | Week | Rnge
110 | W18-2022 | 110
110 | W19-2022 | 120
110 | W22-2022 | 270
Here we are missing weeks for W20 and W21 of 2022
Need some thing like
Emp | Week | Rnge
110 | W18-2022 | 110
110 | W19-2022 | 120
110 | W20-2022 | 0
110 | W21-2022 | 0
110 | W22-2022 | 270
Emp | Week | Rnge
210 | W51-2021 | 1111
210 | W52-2021 | 2312
210 | W3-2022 | 1222
Here we are missing weeks for W1 and W2 of 2022
Need some thing like
Emp | Week | Rnge
210 | W51-2021 | 1111
210 | W52-2021 | 2312
210 | W1-2022 | 0
210 | W2-2022 | 0
210 | W3-2022 | 1222
How to achieve this ?Thanks!
-
Understood but you've not actually answered my question...
You say (or at least imply) that you want the first day of your weeks to be on Sunday. If the current date were 01 Jan 2022, we need to know two things to figure this out for you...
- What is the week number for 01 Jan 2022 and...
- What is the date for the beginning of the week that contains 01 Jan 2022?
The reason I ask is because if I were left to my own devices, 01 Jan 2022 would occur in the last week of 2021 and NOT the first week of 2022.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
The above is the i/p which i receive my client which may having missing weeks data, so i need to populate 0 for missing weeks.
Also, actually it has two requirements, out of which the first one was to consider Sunday as day 1 and count the week based on it.
For second one it is similar to above, if W52 is there and it has possibility of W53 also for some years.. need to generate week calendar from year 2010 to 2040.
Here in the below code for FY 2021 it shows it has W53, but suppose to get until W52.
declare @startdate datetime='2019-01-01'
declare @counter int=1
while @counter<2500
begin
declare @table table(date date, year int, Weeknumber int, day int,
dayname varchar(20), dayyear int, dayweek int,Weekday Varchar(20))
insert into @table
select convert(date,@startdate) date,datepart(yy,@startdate) year,datepart(wk, @Startdate) weeknumber,
datepart(dd, @startDate) day, datename(dd, @startdate) dayname, DATEPART(dd,@startdate) dayofyear,
datepart(dw, @startdate) dayweek,
Datename(WEEKDAY,@startdate) Weekday
select @startdate=dateadd(dd,1,@startdate)
select @counter=@counter+1
end
;with cte as (select Distinct Convert(varchar(2),'W')+ Convert(varchar(5),Weeknumber)+'-'+ Convert(varchar(5),year) weekNo
--,Row_number() over(Partition by Weeknumber,year order by Weeknumber,year) Rn
from @table
--Order by Convert(varchar(2),'W')+ Convert(varchar(5),Weeknumber)+'-'+ Convert(varchar(5),year)
) Select * from cte Order by weekNoWhen used with ISO week, for FY 2021 , not getting w53 it has W52 alone which is correct.
-- Here with ISO_Week
Declare @stDate datetime='01/01/2019'
declare @eddate datetime='12/31/2025'
select Distinct datepart(ISO_WEEK,dateadd(wk, datediff(wk, 0, DATEADD(day,number,@stdate)), 0))
,dateadd(wk, datediff(wk, 0, DATEADD(day,number,@stdate)), 0)
from master..spt_values where type='P'
and DATEADD(day,number,@stdate) <= @eddate -
To be able to determine the week number - you need to know 2 things. You need to know the start of the year and the end of the year (start of next year minus 1). You also need to know the start of the previous year so you can calculate the total number of weeks in the previous year for those dates that are in the current calendar year - but fall before the start of the fiscal year.
The ISO week numbers use a modified calendar year and calculate the first day of the year (day 1 week 1) as the Monday prior to the first Thursday of the year. In the ISO calendar - if the first Thursday is on 1/1, then the first of that year is going to be 12/29 of the previous year.
Before we can begin to provide you a solution - you need to be able to tell us how your calendar is being defined. Based on what I have seen so far - I am assuming you want calendar weeks to align with these: https://savvytime.com/week-number/united-states/2022
If so - the following uses the same type of calculation as is used by ISO and matches up with the calendar at the link above:
Declare @currentDate date = '2022-01-01';
Select *
, current_year = year(@currentDate) + Case When @currentDate < yr.first_curr_year Then -1
When @currentDate >= yr.first_next_year Then 1
Else 0
End
, iso_week = datepart(iso_week, @currentDate)
, current_week = Case When @currentDate >= yr.first_next_year
Then 1
When @currentDate < yr.first_curr_year
Then datediff(day, yr.first_prev_year, yr.first_curr_year - 1) / 7 + 1
Else datediff(day, yr.first_curr_year, @currentDate) / 7 + 1
End
, weeks_prev_year = datediff(day, yr.first_prev_year, yr.first_curr_year - 1) / 7 + 1
, weeks_curr_year = datediff(day, yr.first_curr_year, yr.first_next_year - 1) / 7 + 1
From (Values (dateadd(day, ((datediff(day, -1, datefromparts(year(@currentDate) - 1, 1, 3)) / 7) * 7), -1)
, dateadd(day, ((datediff(day, -1, datefromparts(year(@currentDate) + 0, 1, 3)) / 7) * 7), -1)
, dateadd(day, ((datediff(day, -1, datefromparts(year(@currentDate) + 1, 1, 3)) / 7) * 7), -1))
) As yr(first_prev_year, first_curr_year, first_next_year)I have not tested this fully - and it may not be correct for your usage, but at least it is a starting point.
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 - LearnSQL wrote:
Jeff,
The above is the i/p which i receive my client which may having missing weeks data, so i need to populate 0 for missing weeks.
Also, actually it has two requirements, out of which the first one was to consider Sunday as day 1 and count the week based on it.
For second one it is similar to above, if W52 is there and it has possibility of W53 also for some years.. need to generate week calendar from year 2010 to 2040.
Here in the below code for FY 2021 it shows it has W53, but suppose to get until W52.
declare @startdate datetime='2019-01-01'
declare @counter int=1
while @counter<2500
begin
declare @table table(date date, year int, Weeknumber int, day int,
dayname varchar(20), dayyear int, dayweek int,Weekday Varchar(20))
insert into @table
select convert(date,@startdate) date,datepart(yy,@startdate) year,datepart(wk, @Startdate) weeknumber,
datepart(dd, @startDate) day, datename(dd, @startdate) dayname, DATEPART(dd,@startdate) dayofyear,
datepart(dw, @startdate) dayweek,
Datename(WEEKDAY,@startdate) Weekday
select @startdate=dateadd(dd,1,@startdate)
select @counter=@counter+1
end
;with cte as (select Distinct Convert(varchar(2),'W')+ Convert(varchar(5),Weeknumber)+'-'+ Convert(varchar(5),year) weekNo
--,Row_number() over(Partition by Weeknumber,year order by Weeknumber,year) Rn
from @table
--Order by Convert(varchar(2),'W')+ Convert(varchar(5),Weeknumber)+'-'+ Convert(varchar(5),year)
) Select * from cte Order by weekNoWhen used with ISO week, for FY 2021 , not getting w53 it has W52 alone which is correct.
-- Here with ISO_Week
Declare @stDate datetime='01/01/2019'
declare @eddate datetime='12/31/2025'
select Distinct datepart(ISO_WEEK,dateadd(wk, datediff(wk, 0, DATEADD(day,number,@stdate)), 0))
,dateadd(wk, datediff(wk, 0, DATEADD(day,number,@stdate)), 0)
from master..spt_values where type='P'
and DATEADD(day,number,@stdate) <= @eddateWhat I'm trying to figure out is, if you're bent on using Sunday as the first day of the week, why are you even looking at ISOWK?
The other thing is, I'm trying to figure out what you want to do about the edge cases near the 1st of each year especially since neither 365 nor 366 is evenly divisible by 7. For example, would you label each of the rows in the following table with the week number you want the date to be a part of, please? That will answer a whole lot of questions.
. TheDate DoW WK#
1 2021-12-23 Thu ???
2 2021-12-24 Fri ???
3 2021-12-25 Sat ???
4 2021-12-26 Sun ???
5 2021-12-27 Mon ???
6 2021-12-28 Tue ???
7 2021-12-29 Wed ???
8 2021-12-30 Thu ???
9 2021-12-31 Fri ???
10 2022-01-01 Sat ???
11 2022-01-02 Sun ???
12 2022-01-03 Mon ???
13 2022-01-04 Tue ???
14 2022-01-05 Wed ???
15 2022-01-06 Thu ???
16 2022-01-07 Fri ???
17 2022-01-08 Sat ???
18 2022-01-09 Sun ???
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thanks for helping me out here. Here is the calendar data.
-
Absolutely perfect. That graphic explains it all. I have a high performance idea on how to do this and I'll explore it tonight after work.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... I've got the week number part down to a nice, high speed single formula. I need to add the year in but it' 03:17 here and I have to be up in a few hours , so I'll have to come back to this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm still thrown off by week 53. What? How does this work? This topic is the same as this other one I'm assuming
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
I'm still thrown off by week 53. What? How does this work? This topic is the same as this other one I'm assuming
The week 53 problem is because of the automatic reset of week to week 1 on the first of January of every year, no matter what and whether it occurs on a Sunday or not. I've got code that treats the weeks as whole weeks where that won't happen. In other words, it works exactly like the ISO code but it's based on Sundays and uses Wednesdays to determine which year the edge cases belong to. I've played my code against multiple different years and it appears to be working just as the graphic the OP posted... whole weeks starting on Sunday. Wednesday determine which year the edge cases belong to just like Thursdays do for ISO weeks.
Week 53 is also referred to as a "leap year" because 365 and 366 aren't evenly divisible by 7 and so you start building up an offset that must be reckoned with over time (about once ever 6-7 years, IIRC.
I just need a bit of time to work out the year from that and I'll try to finish that off tonight.
This is going to be useful to other folks, as well, because of the "reset to 1 on the 1st of the year" "feature" that MS built in to keep the functions determinant.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... I think I'm ready for ya! 😀 Post some readily consumable data in the form of a CREATE TABLE statement and some data that's coded to be inserted into that test table and we can put this problem to bed. 😉
Please see the article at the first link in my signature line below for why I ask for such a thing and one of many ways to pull it off.
A couple of important (hopefully final) questions though...
- Do the folks that are providing the data base their weeks on ISOWeeks (always start on Mondays) or do they ALWAYS base the week numbers on Sundays like the graphic you posted seems to indicate?
- And if they use Mondays, do you wanted it all to be converted to Sunday-based weeks, instead?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As a bit of a sidebar, you listed your week numbers as follows...
W51-2021
W52-2021
W3-2022
That's actually a really bad thing for you folks to be doing because they're NOT sortable.
The week numbers above should look like the following:
2021W51
2021W52
2022W03 -- Note the 2 digits!
You could add a dash between the year and the "W" but that's just a waste of space, IMHO.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
declare @startdate datetime='2019-01-01'
declare @cnt int=1
while @cnt<2000
begin
declare @table table(date date, year int, quarter int, month int, monthName varchar(20),Weeknumber int,
dayweek int,Weekday Varchar(20),WeekStartDate date, WeekEndDate date, MonthStartDate date, MonthEndDate date)
insert into @table
select convert(date,@startdate) date,datepart(yy,@startdate) year,datepart(q,@startdate) quarter,
datepart(mm,@Startdate) month, datename(mm, @startdate) monthName,datepart(wk, @Startdate) weeknumber,
datepart(dw, @startdate) dayweek,
Datename(WEEKDAY,@startdate) Weekday,
DATEADD(dd, -(DATEPART(dw, @startdate)-1), @startdate) weekstartdate,
DATEADD(dd, 7-(DATEPART(dw, @startdate)), @startdate) weekenddate,
DATEADD(DAY, -(DAY(@startdate) - 1), @startdate),
dateadd(dd,-1,DATEADD(MONTH, 1, DATEADD(DAY, -(DAY(@startdate) - 1), @startdate) ))
select @startdate=dateadd(dd,1,@startdate)
Set @cnt=@cnt+1
end
select * from @tableA couple of important (hopefully final) questions though...
- Do the folks that are providing the data base their weeks on ISOWeeks (always start on Mondays) or do they ALWAYS base the week numbers on Sundays like the graphic you posted seems to indicate?
It starts with Sunday.
- And if they use Mondays, do you wanted it all to be converted to Sunday-based weeks, instead?
They have used Sunday as starting day.
- The week numbers above should look like the following:
2021W51
2021W52
2022W03 -- Note the 2 digits!
Yes this is agreed as this needs to be modified per working case.
Again Vey thankful for your time and suggestions.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply