Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums  Old Forums  CLOSED - General SQL Server  SIMPLE DATEDIFF PROBLEM

Author  Topic

OBINNA_EKE
Posting Yak Master

234 Posts

 Posted - 2006-07-04 : 07:32:05 The number of days between 1ST JAN 2006 - 31TH JAN 2006 SHOULD BE 31 BUT SQL is returning 30 days why, I need it to return 31 daysSELECT DATEDIFF(day,'2006/01/01','2006/31/01') AS no_of_days

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2006-07-04 : 07:36:32 this is bacause, datediff for same day is 0.select datediff(day, '2006-01-01', '2006-01-01') = 0You will need to + 1 to get the correct number of daysSELECT DATEDIFF(day,'2006/01/01','2006/01/31') + 1 AS no_of_daysKH

OBINNA_EKE
Posting Yak Master

234 Posts

 Posted - 2006-07-04 : 07:38:04 So what your saysing is that when ever I do a datediff for month,years etc I should be adding 1

khtan
In (Som, Ni, Yak)

17689 Posts

 Posted - 2006-07-04 : 07:38:47 Or to find the number of days in a month for a given date`declare @dte datetimeselect @dte = '2006-02-04'select day(dateadd(month, datediff(month, 1, @dte) + 1, -1))-- RESULT : 28`KH

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

 Posted - 2006-07-04 : 07:40:23 quote:Originally posted by khtanthis is bacause, datediff for same day is 0.select datediff(day, '2006-01-01', '2006-01-01') = 0You will need to + 1 to get the correct number of daysSELECT DATEDIFF(day,'2006/01/01','2006/01/31') + 1 AS no_of_daysAgreed. Good to see you switched the 31 to the right place too. And datediff for the same day is 0 because you're really asking for the difference between (for example) '2006-01-01 00:00:00' and '2006-01-01 00:00:00' - which is, of course, 0 days (and 0 minutes, 0 seconds etc).Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part.

OBINNA_EKE
Posting Yak Master

234 Posts

 Posted - 2006-07-04 : 07:44:30 Well what If I want to find SELECT DATEDIFF(day,'2006/01/01','2006/31/01') AS no_of_daysFor me to get the correct days I still need to add 1 for it to be correct YES OR NO

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

 Posted - 2006-07-04 : 07:46:18 khtan gave you the answer in his first response: YES.Do you understand why?Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part.

OBINNA_EKE
Posting Yak Master

234 Posts

 Posted - 2006-07-04 : 07:47:29 Thanks you all, this DateDiff is quite decieving if you don't check it properly

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

 Posted - 2006-07-04 : 20:21:48 quote:Originally posted by OBINNA_EKEThanks you all, this DateDiff is quite decieving if you don't check it properlyDATEDIFF is correct. The difference between 1 and 31 is 30. That's just basic arithmetic. Try it:`select [diff] = 31-1diff ----------- 30`This is probably the simplest way to get the last day of a month.`declare @dt datetimeselect @dt = '2004-02-04'select [Last Day] =day(dateadd(mm,datediff(mm,-1,@dt),-1))Last Day ----------- 29`CODO ERGO SUM