0

I am trying to know week difference between two dates. It looks like SQL server do round off and gives the different result for me. But I want to calculate if they crossed 7, 14, 21 days.

I have stored a week difference in a database as like 1 or 2, etc. So I would like to use DATEDIFF with WEEK without round off.

Expected Result 0, But returns 1

 SELECT DATEDIFF(WEEK, '2014-07-08 10:15:00', '2014-07-14 09:00:00'); --1

Expected Result 1, But returns 2

 SELECT DATEDIFF(WEEK, '2014-07-08 10:15:00', '2014-07-20 09:00:00'); --2

Also same problem with MONTH, expected behaviour for below is 0. But gives 1

 SELECT DATEDIFF(MONTH, '2014-07-15 10:15:00', '2014-08-12 09:00:00')  --1

How can I get the correct week/month difference?

Community
  • 1
  • 1
Billa
  • 5,226
  • 23
  • 61
  • 105
  • 1
    You could look at this similar question? http://stackoverflow.com/questions/1106945/calculating-number-of-full-months-between-two-dates-in-sql – Richard Hansell Jul 08 '14 at 10:01

4 Answers4

2

According to MSDN, DATEDIFF "Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate."

In your first case datepart(week,'2014-07-08 10:15:00') is 28 and datepart(week,'2014-07-14 09:00:00') is 29, so your result should be 1 as indeed it is. Similarly for the other cases.

I suppose you could convert the datetime values to bigint and then divide the difference by 7 to get weeks, if that is what you are looking for, like so:

select (cast(@end as bigint) - cast(@start as bigint))/7

EDIT: DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

Thanks to @Serpiton for this info.

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • 2
    Just a little note found in [another](http://msdn.microsoft.com/en-us/library/ms181598.aspx) page of MSDN you may want to add to the answer: `DATEDIFF` always uses Sunday as the first day of the week to ensure the function is deterministic. – Serpiton Jul 08 '14 at 09:56
  • So what is the solution for my question? – Billa Jul 08 '14 at 10:02
  • `SELECT cast('2014-07-08 10:15:00' as bigint)- cast('2014-07-14 09:00:00'as bigint)/7` gives `Error converting data type varchar to bigint` – Billa Jul 08 '14 at 10:35
  • That's because you are passing a text string to it, not a variable of type `datetime`. I assume your actual columns/variables are of `datetime` type? If not, I would suggest you make them `datetime` type first. – shree.pat18 Jul 08 '14 at 10:49
  • `Declare @sd smalldatetime='2014-07-08 10:15:00'; Declare @ed smalldatetime='2014-07-14 09:00:00'; SELECT cast(@sd as bigint)- cast(@ed as bigint)/7` gives result as 35850.. Whats this number first? I was expecting 0 :(.. i mean 0 week – Billa Jul 08 '14 at 11:32
  • Brackets! You need `SELECT (cast(@sd as bigint)- cast(@ed as bigint))/7` i.e. the result of subtraction inside bracket and then divided by 7 – shree.pat18 Jul 08 '14 at 11:33
0

You can try DAY instead of WEEK in the DATEDIFF function, and then divide by 7 to get the complete week

SELECT DATEDIFF(DAY, '2014-07-08 10:15:00', '2014-07-20 09:00:00') / 7
Kiran Hegde
  • 3,651
  • 1
  • 16
  • 14
  • But if I am dealing with Month, instead of Week, Do i need to divide by 30/31/28? – Billa Jul 08 '14 at 09:49
  • ` SELECT DATEDIFF(MONTH, '2014-07-15 10:15:00', '2014-08-12 09:00:00')` also gives 1. How to deal this. – Billa Jul 08 '14 at 09:52
  • Question is how do you want this to return, do you want take 30/31/28 days as month, or fromdate and todate spans over one complete month. for example jan 15 to mar 15, should it return 1 because only feb is the complete month in the range, but if we take from the date perspective it is 2. – Kiran Hegde Jul 08 '14 at 09:58
0

Solution for your question

SELECT DATEDIFF(DD, '2014-07-08 10:15:00', '2014-07-14 09:00:00')/7
SELECT DATEDIFF(DD, '2014-07-08 10:15:00', '2014-07-15 09:00:00')/7
SELECT DATEDIFF(DD, '2014-07-08 10:15:00', '2014-07-20 09:00:00')/7
SELECT DATEDIFF(DD, '2014-07-08 10:15:00', '2014-07-22 09:00:00')/7
Azar
  • 1,852
  • 15
  • 17
0

Kiran Hedge's answer cover the weeks, for the months you can use 

declare @datestart datetime = '2014-07-09 10:15:00'
declare @datestop datetime = '2014-08-08 09:00:00'

SELECT DATEDIFF(MONTH, @datestart, @datestop)
     - CAST(((DATEPART(DAY, @datestart))
      / (DATEPART(DAY, @datestop) + 1)) AS BIT)

If the days are the same it'll count as a month, the CAST to BIT is to reduce every number to 1.

Serpiton
  • 3,676
  • 3
  • 24
  • 35
  • `SELECT DATEDIFF(MONTH, '2014-07-15 10:15:00', '2014-08-15 09:00:00')- (DATEPART(DAY, '2014-07-15 10:15:00')/ DATEPART(DAY, '2014-08-15 09:00:00'))` It is not giving 1 :( – Billa Jul 08 '14 at 10:32