Hi

I am querying a timesheet database and am pulling out decimal values that need to be converted to time format HH:MM

I have used the CAST command to change these values. The problem is if any time value is >24, I get a format in microsoft excel of HH:MM:SS not HH:MM.

CASE WHEN CAST(SUM(time_recs.time_amount) as char(12)) = '36.25' THEN '36:15'
WHEN CAST(SUM(time_recs.time_amount) as char(12)) = '36.27' THEN '36:16'

Ex. 36.52 would be '36:15:00' not '36:15', and one has to have a huge number of CAST statements, one to match every decimal to every minute of every hour

Is there any way to do this without having to break the times down to less than 24 hour increments

Thanks for the tip, just a few modifications were necessary, here's the code that implements:

CONVERT(varchar, CAST(SUM(time_recs.time_amount) AS INT)) + ':' +

CASE
WHEN CONVERT(varchar, (SUM(time_recs.time_amount) - CAST(SUM(time_recs.time_amount) AS INT))*60) = '0' THEN '00'

Something like:

CONVERT(VARCHAR, CAST(SUM(time_recs.time_amount) AS INT)) + ':' +
CONVERT(VARCHAR, SUM(time_recs.time_amount) -
CAST(SUM(time_recs.time_amount) AS INT))
?
--
Jacco Schalkwijk
SQL Server MVP


[quoted text, click to view]
any time value is >24, I get a format in microsoft excel of HH:MM:SS not
HH:MM.
[quoted text, click to view]
number of CAST statements, one to match every decimal to every minute of
every hour.
[quoted text, click to view]