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, 2 months 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. can any one help me out on this please.You are correct. how can i achieve that using this this query. When i run this query ,at the end of the 2013 it gives the result as,įor dates 29 ,30,31st it should give 53. This query gives date, weekday and week_num for 2 years 20, To_char(next_day(mydate,'sunday'),'iw') as week_numįROM ( SELECT TRUNC (SYSDATE, 'yy') - 1 + LEVEL AS mydateĬONNECT BY LEVEL <= (SELECT TRUNC (ADD_MONTHS (SYSDATE, 24), 'yy') I have a query that starts with 01 when year starts but it gives problem in the end of the year. How can i achieve this, can anyone please help me out on this. again in the next year it should start with '01'. in the end date of the year it should not be 'saturday' but week number should end with last date of the year. so next week number starts with sunday and ends with saturday ,continously. So that week number should start with 01 when the year starts and it should end with week end date(that is first saturday of the january month). I have the requirement to find the week number for the calender.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |