Datedifference - Please help

Hi,
I have a problem with the following:
I have 2 dates in the following format mm/dd/yyyy hh:mm:ss AM.
I would like to measure the difference between 2 dates (Date1 and Date2).
When I use the following expression "DATEDIFF(d, Date1, Date2)" I get a value
without decimals. How can I have a value with decimals like e.g. 5.38
Please help!!!
Thanks!
DHLSounds like a SQL problem, so perhaps check with sqlserver.programming, not
ssrs.
DATEDIFF takes one portion of a date, so you can pick hours, days, minutes,
etc. To quote the BOL (via a google search with just "datediff")
http://msdn2.microsoft.com/en-us/library/aa258269(sql.80).aspx
"Returns the number of date and time **boundaries** crossed between two
specified dates."
If you want a decimal value where the days is before the decimal, and the
% of a day is after, then you can just use floats like this (using the ".0"
is important, it tells us that it is a floating point number. You could
also convert(float,24), but that is ugly =)
declare @.date1 datetime; set @.date1=getdate()
declare @.date2 datetime; set @.date2=dateadd(hour,-5,(getdate()-1))
select
5/24.0
, convert(float, @.date1 - @.date2)
, datediff(hour,@.date1,@.date2)/24.0 -- only hour resolution
Full days are before, and % of a day (hours/24) is after the decimal. Datediff
will only give you 'to the hour' results, because that is what it measures.
So if it is 1 day, 2 hours and 23 minutes, that will show up the same as
1d 2h 00m, etc.
Using convert returns a POSTIVE number becase @.date1 is greater than @.date2.
DateDiff understands what you are trying to do, so it tells you it is -1.2
days difference. Swap them as you see fit, or use ABS()
HTH,
// Andrew
> I have a problem with the following:
> I have 2 dates in the following format mm/dd/yyyy hh:mm:ss AM.
> I would like to measure the difference between 2 dates (Date1 and
> Date2). When I use the following expression "DATEDIFF(d, Date1,
> Date2)" I get a value without decimals. How can I have a value with
> decimals like e.g. 5.38
> Please help!!!
> Thanks!
> DHL
>|||DHL,
You could also do a DATEDIFF with say hours and then convert it to days to
get a decimal value.
Reeves
"DHL" wrote:
> Hi,
> I have a problem with the following:
> I have 2 dates in the following format mm/dd/yyyy hh:mm:ss AM.
> I would like to measure the difference between 2 dates (Date1 and Date2).
> When I use the following expression "DATEDIFF(d, Date1, Date2)" I get a value
> without decimals. How can I have a value with decimals like e.g. 5.38
> Please help!!!
> Thanks!
> DHL