![]() 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). 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. 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. It extracts the year part and the week part out of a date and creates a week number like YYYY_WW. ![]() 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)) Ideally, I wouldn't like to see week 53 at all. 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). Monday to Sunday would be good for me to consider a week. This reply was modified 2 years, 1 month ago by ScottPletcher.SELECT CASE WHEN Jan_01_day = 0 /*Mon*/ THEN Jan_01ĮLSE DATEADD(DAY, -DATEDIFF(DAY, 0, Jan_01) % 7, Jan_01) END AS first_Monday SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, date), 0) AS Jan_01,ĭATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, date), 0)) % 7 AS Jan_01_day * show the new calcs needed to gen yyyy_ww */ĬAST(DATEDIFF(DAY, first_Monday, date) / 7 + 1 AS varchar(2)) AS yyyy_ww_new * create table of the sample dates you provided, adding another date to help verify date logic */ See if these calcs give you results more like what you want.Įdit: Note that the calcs are completely independent of any/all date and language and DATEFIRST setting and will work correctly under ANY of them. That will be different for calendar year vs fiscal year vs retail, etc. To count the number of weeks for a year - you need to define what the first of the year is for your calendar. ![]() ![]() 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. 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. The end of 2021 is 2022-01- only has 52 weeks where 2020 has 53 weeks. ISO week sets the week number in the previous/following years also - which means for year 2020 week 53 ends on and week starts on. If the OP wants something else, say Sunday to Saturday, then some sort of offset is required. I was trying to make the point that an iso_week runs Monday to Sunday. Week 41 is the Current Week Number 2021. Continue reading to get intrigue details about Current Week Number 2021.You are correct. Since the emphasis and the importance were given to the Current Week Number of The Month in the Year, people indulge in search of the Current Week Number 2021. Knowing the Current Week Number 2021 is the primary concern for the people of today's scenario. People in this world of scientific influence began to show interest in the minor details of the day.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |