Thanks Terri. I looked at the tag, and it is exactly the functionality I
want, but I would prefer to calculate this in SQL rather than in CF, because:
1. less load on CF
2. i am using some generic code to show the SQL query to the browser,
so i can't incorporate the call to CF_ConvertMinutes easily.
3. it seems like something sql should be able to handle easily,
and it bugs me that i can't figure it out ;-)
any other suggestions?
greg
At 12:48 PM 9/1/00 +0000, Terri Stocke wrote:
>Greg,
>
>I highly recommend the CF_ConvertMinutes custom tag from the Allaire
>Gallery. It's extremely simple to use, and will do exactly what you are
>looking for...
>
>Terri
>
>
>----Original Message Follows----
>From: Greg Saunders <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: [EMAIL PROTECTED]
>Subject: how to calculate elasped time in SQL?
>Date: Fri, 01 Sep 2000 07:21:27 -0500
>
>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.
>
>_________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>Share information about yourself, create your own public profile at
>http://profiles.msn.com.
>
>------------------------------------------------------------------------------
>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.
------------------------------------------------------------------------------
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.
