| Author |
 Topic  |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
 Posted - 07/04/2006 : 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)
Singapore
16746 Posts |
Posted - 07/04/2006 : 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
KH
|
 |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 07/04/2006 : 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)
Singapore
16746 Posts |
Posted - 07/04/2006 : 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
KH
|
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 07/04/2006 : 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 - 07/04/2006 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 07/04/2006 : 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 - 07/04/2006 : 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)
USA
6997 Posts |
Posted - 07/04/2006 : 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 |
|
|
| |
Topic |
|
SIMPLE DATEDIFF PROBLEM
Forum Locked
