SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 SIMPLE DATEDIFF PROBLEM
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/04/2006 :  07:32:05  Show Profile
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)

Singapore
17681 Posts

Posted - 07/04/2006 :  07:36:32  Show Profile
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





KH

Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/04/2006 :  07:38:04  Show Profile
So what your saysing is that when ever I do a datediff for month,years etc I should be adding 1
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/04/2006 :  07:38:47  Show Profile
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



KH

Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/04/2006 :  07:40:23  Show Profile
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.
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/04/2006 :  07:44:30  Show Profile
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
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/04/2006 :  07:46:18  Show Profile
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.
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/04/2006 :  07:47:29  Show Profile
Thanks you all, this DateDiff is quite decieving if you don't check it properly
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/04/2006 :  20:21:48  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000