SELECT CAST('20001230' AS DATETIME) UNION ALL SELECT CAST('20001229' AS DATETIME) UNION ALL SELECT CAST('20001228' AS DATETIME) UNION ALL SELECT CAST('20001227' AS DATETIME) UNION ALL SELECT CAST('20001226' AS DATETIME) UNION ALL SELECT CAST('20001225' AS DATETIME) UNION ALL Here is an example: WITH TestData(SomeDate) AS ( This is trivial thanks to the day-of-week value. By just looking at the chart we come up with the following equation: + DATEPART(dw, SomeDate) - 1 - 1) % 7 + 1Ģ) Calculate the Monday and Sunday for given date The last column contains the ideal day-of-week value for Monday to Sunday weeks*. The following table summarizes the possible values: | 1 | 2 | 3 | 4 | 5 | 6 | 7 | DOW | ) returns a number, 1.7, relative to DATEFIRST setting ( docs). Let us break the problem down to two parts: This method would be agnostic of the DATEFIRST Setting which is what I needed as I am building out a date dimension with multiple week methods included. ,DATEADD(DAY, 6 - + + % 7, as FridayEndOfWeek ,DATEADD(DAY, 0 - + + % 7, as FridayStartOfWeek ,DATEADD(DAY, 6 - + + % 7, as MondayEndOfWeek ![]() SET DATEFIRST 6 - notice this is saturdayĭATEADD(DAY, 0 - + + % 7, as MondayStartOfWeek ![]() Here is a working example: DECLARE INT = 1 ![]() | DATEFIRST VALUE | Formula Value | 7 - DATEFIRSTVALUE - 1īut you don't have to remember that table and just the formula, and actually you could use a slightly different one too the main need is to use a value that will make the remainder the correct number of days. A way to arrive at the value you need is Value Needed = 7 - (Value From Date First Documentation for Desired Day Of Week) - 1Īnd here is a table that lays it out for you. The formula works for days other than Sunday and Monday but you need to use different values for where the 5 is.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |