It can have days from the previous year and days from the next year included in the “ISO Year”. It always starts on a Monday and always ends on a Sunday, even for the 1 st or last weeks of the year. Of course, that also means that the ISOWEEK doesn’t ever match the Calendar year. They’re also supposed to end on the same day but the day before any 1 st of January, which is the last day of the year, can also be any day of the week.Īgain, ISOWEEK doesn’t have this problem. In most cases when working with weekly periods, the weeks are supposed to all start on the same day. In plain English, that means that no matter what day of the week it is, the week number will ALWAYS be reset to “1” on the 1 st of January, no matter the year. January 1 of any year defines the starting number for the week datepart. If you read the documentation at the following URL: The “First Week” ProblemĪctually, even working with Sundays is an issue with DATEPART(wk). They don’t bother to tell you the reason… they simply don’t list ISOWK as one of the supported date-types for use with DATEDIFF in the documentation. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.ĭATEDIFF_BIG() has the same problem and you can find that in the article at the following link: Īs it turns out, DATEDIFF(ISOWK) will also not work for the same reason. Specifying SET DATEFIRST has no effect on DATEDIFF. Here’s the quote of the pertinent information from the article at the link above (the emphasis is mine)… You can read more about the issue at the following link: That means that none of that other clever periodic code that uses DATEDIFF() can work with the WEEK (wk) date-part UNLESS you want Sunday to be the first day of the week. In order to keep DATEDIFF(wk) deterministic, DATEDIFF(wk) only uses Sunday as the first day of the week regardless of the DATEFIRST setting. There are some super easy “Beginning of Period” calculations to be had with all the other date-parts that work using DATEDIFF(). Even the ISOWEEK date-part can’t get around this problem The “Difference” Problem Working with WEEKs is especially painful because the 365 or 366 days in a year are NOT evenly divisible by 7, the remainder of which must eventually be accounted for by including the occasional 53 week “Leap Year”, which doesn’t match the normal solar leap years. Let's first take a look at some of those problems so we know what to avoid, if we can. That’s actually the source of several problems when it comes to calculating things like the Start of Week Date, the End of Week Date, and a whole lot more. No other date-part in SQL Server always needs to start on the same day of the week. The exception to that observation is when we have to work with WEEKs. Thanks to some really clever temporal functions that are built into SQL Server, working with dates and times in SQL Server is pretty easy. You don’t know what you’re missing, though.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |