Adding a Decimal Value to a DateDiff Statement

Question

Post reply

Adding a Decimal Value to a DateDiff Statement

staindshady_03

SSChasing Mays

Points: 642
More actions
July 26, 2011 at 6:19 pm

#254352

I am sure there is an easy way to do this but I cannot figure it out, I am trying to add a decimal value to a datediff statement & I still get the outcome as 0 as opposed to when I try to add a whole number such as 1 or 5, I get the desired outcome with 1 or 5 added.
Here's my code:
Select
StartDate, EndDate, CASE Type WHEN 'HALF' THEN DATEDIFF (d, StartDate, EndDate + 0.5) - (select DT from TableB)
ELSE
DateDiff (d,StartDate, EndDate + 1)
END
From Table A
Where ID=@ID AND SOLO='Complete'
The scenario with the +1 work, but where I try to add + 0.5 with the HALF scenario only returns a 0.
I tried to do
Datediff (d,StartDate,dateadd(hour,12,EndDate))
But that just gave me a similar 0 result instead of 0.5
Any ideas?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Without knowing the actual values for your StartDate and EndDate, the following works:

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2011-07-26 11:50:33.420' --arbitrary value

SET @EndDATE = '2011-07-26 21:50:33.420' -- arbitrary value

SELECT Datediff(d,@StartDate,dateadd(hour,12,@EndDate)) AS 'Answer'

Results:

Answer

1

For further assistance please post some sample data.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

staindshady_03 SSChasing Mays Points: 642 More actions · Answer 2

thanks for your response, but I need it to return a 0.5 instead of 1 hence the CASE. But I figured that the datediff only can return int values, so I am going to try to put the + 0.5 outside the datediff function & see if that works.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What I see is that you don't understand what the DATEDIFF function is doing. To help you, please read the following article: DATEDIFF Function Demystified.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 4

DATEDIFF doesn't work the way most people think. It works on the borders of days (ie: midnight). For example, even though the following dates are only 3 milliseconds apart, DATEDIFF still says they're 1 day apart because it crosses a "day border".

SELECT DATEDIFF(dd,'2011-01-01 23:59:59.997', '2011-01-02 00:00:00')

Likewise, even though the following dates are only 3 milliseconds away from being 48 hours apart, DATEDIFF only returns "1" because only one "day border" was crossed.

SELECT DATEDIFF(dd,'2011-01-01 00:00:00', '2011-01-02 23:59:59.997')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

staindshady_03 SSChasing Mays Points: 642 More actions · Answer 5

I understand this, I guess I just wasn't being clear. Maybe if I illustrate a scenario that would explain it better:

____________________________________________________________________________________

StartDate | End Date | Type | TOTALDays

07/26/2011 12:00:00:00 AM 07/26/2011 12:00:00:00AM HalfDay 0

_____________________________________________________________________________________

Output(To illustrate the total days to add upto 0.5 as it's a half day)

___________________________________________________________________________________

StartDate | End Date | Type | TotalDays

07/26/2011 12:00:00:00 AM 07/26/2011 12:00:00:00 AM HalfDay 0.5

___________________________________________________________________________________

Based on the case of HalfDay, if the datediff returns a value as 0, I want to add 0.5 to it so that it displays the total days at 0.5

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

staindshady_03 (7/26/2011)
I understand this, I guess I just wasn't being clear. Maybe if I illustrate a scenario that would explain it better:
____________________________________________________________________________________
StartDate | End Date | Type | TOTALDays
07/26/2011 12:00:00:00 AM 07/26/2011 12:00:00:00AM HalfDay 0
_____________________________________________________________________________________
Output(To illustrate the total days to add upto 0.5 as it's a half day)
___________________________________________________________________________________
StartDate | End Date | Type | TotalDays
07/26/2011 12:00:00:00 AM 07/26/2011 12:00:00:00 AM HalfDay 0.5
___________________________________________________________________________________
Based on the case of HalfDay, if the datediff returns a value as 0, I want to add 0.5 to it so that it displays the total days at 0.5

I'm sorry, but your example fails to enlighten me. Both the start date and end date are the same.

staindshady_03 SSChasing Mays Points: 642 More actions · Answer 7

exactly, which would make the result 0. To that 0 I want to add 0.5, which would make the result 0.5.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

staindshady_03 (7/26/2011)
exactly, which would make the result 0. To that 0 I want to add 0.5, which would make the result 0.5.

Why? What is the business case for this when they are exactly the same?

staindshady_03 SSChasing Mays Points: 642 More actions · Answer 9

to count days as half or full. for example if you do a datediff between two dates, it counts the difference but it doesn't count the date you start the calculation on. Such as Monday through Friday would be counted as 4 days in a week instead of 5, hence you have to add a 1 to get accurate calculations.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

staindshady_03 (7/26/2011)
to count days as half or full. for example if you do a datediff between two dates, it counts the difference but it doesn't count the date you start the calculation on. Such as Monday through Friday would be counted as 4 days in a week instead of 5, hence you have to add a 1 to get accurate calculations.

Two things, true the number of days between Monday and Friday is 4, just as the difference between 1 and 5 is 4. TO count the starting point you have to add 1. That I understand. But you still havent explained why the difference between 7/26/2011 12:00:00 AM and 7/26/2011 12:00:00 AM should be 0.5 when there is no difference.

Wait, I think I understand The difference between Monday and Monday including the start point is 1. So the half day occurs if you are counting the 12 hour periodes that exist between 7/26/2011 12:00:00 AM and 7/27/2011 12:00:00 AM. Is this correct?

staindshady_03 SSChasing Mays Points: 642 More actions · Answer 11

staindshady_03

SSChasing Mays

Points: 642

July 26, 2011 at 10:33 pm

#1358946

yup that sounds right.

drew.allen SSC Guru Points: 77011 More actions · Answer 12

Jeff Moden (7/26/2011)
DATEDIFF doesn't work the way most people think. It works on the borders of days (ie: midnight).

I would amend this to say that it works on the borders of the specified time interval. While days are probably the most common, months and years make up significant portions.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 77011 More actions · Answer 13

DateDiff will always produce an integer result. You need to move your addition outside of the DateDiff function.

CASE Type WHEN 'HALF' THEN DATEDIFF (d, StartDate, EndDate) + 0.5

You're thinking of Datediff as equivalent to subtraction/addition, but it's not. If it were, it wouldn't matter which order you do the operations in. Since it's not, it most certainly does matter which order you do the operations in.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

staindshady_03 SSChasing Mays Points: 642 More actions · Answer 14

Thanks guys, I solved this yesterday by putting the decimal value outside the function as specified. It worked for me, thanks for all the input.

Adding a Decimal Value to a DateDiff Statement

Cookies on SQLServerCentral