0

I have a query that selects all items for the given week and sets their date to be the start day of that week. In some cases the query sets an incorrect date value for the given item and I have traced the problem to be in either DATEDIFF or DATEADD functions.

The query is 

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, DateTimeValue), 0) AS NewDateTimeValue

Let's take a date March 27 2016 as an example. The DATEDIFF returns value of 6065 and the DATEADD with that value returns March 28 2016.

For the date March 26 2016, the DATEDIFF returns 6064 and the DATEADD March 21, 2016.

To me this sounds like a FirstDayOfWeek issue and that SQL Server thinks Sunday is the first day of a week and thus giving different values for Sunday and for Saturday (March 27, 2016 is Sunday).

I tried to set the first day of the week by

SET DATEFIRST 1

but that didn't make any difference and SQL returned the same results.

So what causes the SQL function to behave like this and any ideas how to fix it?

Antti Simonen
  • 964
  • 1
  • 14
  • 25

2 Answers2

0

I found out that DATEDIFF doesn't respect the DATEFIRST setting and always assumes the week starts on Sunday. This is the reason why my sample case works as it works. This question has some suggested workaround: Is it possible to set start of week for T-SQL DATEDIFF function?

Community
  • 1
  • 1
Antti Simonen
  • 964
  • 1
  • 14
  • 25
0

This is happening because you are finding the no. of weeks from date 0 to the supplied date. 0 is actually '1900-01-01' which was a Monday. Therefore,DATEDIFF finds the number of completed weeks from this date until the supplied data. Thats why DATEDIFF for March 27 2016 returns 6065 (as it is the end of a week) and March 26 2016 returns 6064 (as it is still not the end of a week).
This is already explained in this link - Get first day of the week

Community
  • 1
  • 1
Amit Sukralia
  • 950
  • 1
  • 5
  • 13