0

My requirement is that I want to find business-week-ending (not the calender week) given a DATE column from the sales table in MSSQL.

Using different techniques I was able to find the [Calender] week-endings (and week-starting) dates corresponding to DATE in the table.

Since our business week ends on Wednesday [DOW 3 or 4 depending when the week started], I tried to deduct number of days from the week ending dates to pull it back to Wednesday. The idea did work pretty good with a flaw. Works fine as long as the Date in the table is greater than DOW 3 or 4. Any suggestion?

SELECT DateAdd(wk, DateDiff(wk, 0, Recons_Sales_Details.Recons_Date), 0) + 2
Wandering Fool
  • 2,170
  • 3
  • 18
  • 48
Honda
  • 1
  • 1
  • Can you please post your code that you have tried? – FutbolFan Aug 09 '15 at 17:48
  • Your code uses Monday - Sunday week because day zero (1.1.1900) was Monday. You can change that by changing the both 0s to some other date, which is for example Thursday – James Z Aug 09 '15 at 18:05
  • @JamesZ: 20130102 is Wed. this code does ends the week on Wednesday. but at the same time it pusshes back 20150801 back to weekending 7/29. DateAdd(week, DateDiff(week, '2013-01-02', Recons_Sales_Details.Recons_Date), '2013-01-02'), – Honda Aug 09 '15 at 18:36
  • Sorry, you'll of course need to use days / 7, DateAdd(week, DateDiff(day, '2013-01-03', Recons_Sales_Details.Recons_Date) / 7, '2013-01-03') – James Z Aug 09 '15 at 19:00

1 Answers1

0

You need to look into SET DATEFIRST to do this:

SET DATEFIRST 4  --4 is Thursday week start

SQL Fiddle Demo

FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • **DATEFIRST** was the very first appealing option I liked the most, however my limitation with this option is that this production (cloud) server has multiple clients with different business industries and geography. And as far as I understand DATEFIRST will change the server-calendar. – Honda Aug 09 '15 at 18:29
  • @Honda I am not sure if `DATEFIRST` actually changes anything else but the adhoc query that you run. Just like `SET NO COUNT ON`, I believe it only applies to a specific session/transaction. – FutbolFan Aug 10 '15 at 00:35