summing a field in minutes to hours in SQL 2012

I have a integer field in minutes that I need to convert to hours and then sum the field  I have tried the following code:

left(convert(time,DATEADD(minute,pe.appt_length,0)),5) as APPT_LENGTH_HRS,

this will convert the field to hours but it won't sum when the field is grouped.  I have two appointments that are 30 minutes long so the total should be an hour.  The apt_length_hrs field isn't summing.  I get an error when I try to put a sum in the code. 

  

 

  
September 11th, 2015 3:32pm

Hi aaangela,

According to your description, you convert minutes into hours, then you find you can't sum the hours. Right?

In this scenario, when you use the convert() function, the data type of this field became datetime. After that you use the left() function, this will resolve the field into string value so that you can't sum it. For your requirement, you can use DATEPART() function after you convert the data field. It will resolve the result into numeric type.

Reference:
DATEPART (Transact-SQL)

Regards,

Free Windows Admin Tool Kit Click here and download it now
September 13th, 2015 11:28am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics