SIMPLE DATEDIFF PROBLEM - SQL Server Forums

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 days

SELECT 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') = 0

You will need to + 1 to get the correct number of days
SELECT DATEDIFF(day,'2006/01/01','2006/01/31') + 1 AS no_of_days

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	datetime

select	@dte	= '2006-02-04'
select 	day(dateadd(month, datediff(month, 1, @dte) + 1, -1))
-- RESULT : 28

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-04 : 07:40:23

quote:
Originally posted by khtan

this is bacause, datediff for same day is 0.

select datediff(day, '2006-01-01', '2006-01-01') = 0

You will need to + 1 to get the correct number of days
SELECT DATEDIFF(day,'2006/01/01','2006/01/31') + 1 AS no_of_days

Agreed. 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 Randall
www.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_days

For 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 Randall
www.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_EKE

Thanks you all, this DateDiff is quite decieving if you don't check it properly

DATEDIFF is correct. The difference between 1 and 31 is 30. That's just basic arithmetic. Try it:

select [diff] = 31-1

diff        
----------- 
30

This is probably the simplest way to get the last day of a month.

declare @dt	datetime
select	@dt	= '2004-02-04'

select [Last Day] =day(dateadd(mm,datediff(mm,-1,@dt),-1))

Last Day    
----------- 
29

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources