I'd like to calculate in SQL the elapsed time between two dates, so the
result would be something like:
2 days, 6 hours, 32 minutes, 15 seconds
but I can't figure out how to do it easily. What I'd really like is to use
some built-in SQL conversion function, like this: CONVERT(char,
date2-date1, 8). This calcuates the TIME between dates nicely, but it
truncates any difference in days, and I couldn't find any other conversion
that was closer.
I've tried using dateDiff with each datepart, like this:
dateDiff(day,date1,date2), dateDiff(hour,date1,date2), etc. But this
doesn't work, because if date1 and date2 differ by 23 hours (for example),
it still returns a 1 day difference if date1 and date2 fall on different days.
My current solution is this:
convert(char(2), floor(convert(float, date1-date2)))
+ 'days, '
+ CONVERT(char, date1-date2, 8)
which works, but feels extremely clunky, especially since I'd like to
repeat this "elapsed time" functionality through many queries.
I realize I could do this in CF by taking the difference in seconds and
then using a combination of division and MOD, but I'm really looking for a
concise way to do this in SQL. Any suggestions?
Is it possible to encapsulate this functionality in SQL, so that I could
call something like:
SELECT myElaspedTime(date1, date2)
FROM table...
greg
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
