Convert the number 13.50 to 1:30pm

# Convert the number 13.50 to 1:30pm

• Thursday, March 14, 2013 11:54 PM

I have a query that pulls an integer from a field for the start time of a session.

for example: The data is stored as 810, so I divide this by 60 and get 13.5, which is 1:30pm

How can I get the data to display as 1:30pm?

I've already done the first bit to get it to 13.5 (see below), but need to format further

`SELECT     tblAppointments.fldAppStart/60 AS StartTime`

Thanks

### All Replies

• Friday, March 15, 2013 1:07 AM

Here's one way to do it.  You can use integer division to get the number of hours.  And Mod to get the leftover minutes.  And then concatenate them and cast to a time datatype.  Then you should be able to format the time datatype as needed in RS.  Or you could leave out the time cast, and just return the string.  Something like this:

```declare @minutes int = 810
SELECT
(CONVERT(char(2), @minutes / 60) + ':' + RIGHT('0' + CONVERT(char(2),(@minutes % 60)),2)) as timeString
,CONVERT(time,(CONVERT(char(2), @minutes / 60) + ':' + RIGHT('0' + CONVERT(char(2),(@minutes % 60)),2))) as timeDT```

Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Friday, March 15, 2013 2:13 AM

Hi Brent, thanks for the Reply. Your solution definitely converts the number 810 to 13:30 which is great

Sorry, I should have given more information. The value 810 is simply the first value in a long list of data. (eg need to convert 810, then 990, then 630, then 720, and so on...)

I'm a bit of a novice, so I don't understand how to take each value returned with the query and convert it to DT

I need to be able to plug the value returned in the field tblAppointments.fldAppStart into the rest of the SELECT statement so that it converts each number.

Make sense?

• Friday, March 15, 2013 3:26 AM

I was just using that variable as an example since I don't have your table to select from.  You can substitute your column name everywhere "@minutes" appears in the expression.

Let me know if that works for you.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Friday, March 15, 2013 7:09 AM

Hi Brent, thanks for the response. I tried substituting the column name everywhere that "@mintues" appears but to no avail. Is it something to do with declaring the variable? What should the syntax be for the first line in your code (ie before the SELECT expression)? Instead of 'declare @minutes int = 810' it should say ?? Thanks John
• Friday, March 15, 2013 4:26 PM

you can remove the declare altogether.  I was simply using that to be able to pass the exact value you provided to that variable as an example, since I don't have your table or columns to work with.

you should be able to simply copy the expression that produces the result you wanted (CONVERT(....)) and use that (in combination with your column) in your original select statement.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Friday, March 15, 2013 4:48 PM

In your query that retrieves the integer field you want to convert, add Brent's example into the existing SELECT statement:

(Assuming the integer column is named StartTime. Please change the below statements to insert your actual column name in place of StartTime.)
If you want it as a DateTime datatype:
,CONVERT(time,(CONVERT(char(2), StartTime / 60) + ':' + RIGHT('0' + CONVERT(char(2),(StartTime % 60)),2))) as timeDT

If you want it to look like a DateTime but not be a DateTime (returned value is actually a string):
(CONVERT(char(2), StartTime / 60) + ':' + RIGHT('0' + CONVERT(char(2),(StartTime % 60)),2)) as timeString

One of the above 2 lines should be inserted into your existing query's select statement. If this helps to resolve the broblem, please mark Brent's post as answer and vote this one as helpful.

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou

• Friday, March 15, 2013 4:56 PM

Hi,

CStr(IIF(X<60, 12, IIF(X<720, X\60, IIF(X<780, 12, (X\60)-12)))) + " " + IIF(X % 60 < 10, "0", "" ) + CStr(X % 60) + " " + IIF(X < 720 , "am","pm")

X is the number of minutes you want to convert (From 0 to 1439)

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

• Saturday, March 16, 2013 6:07 AM

Hi All - thanks for your help, but still no luck. To make things simple I'll show you the SELECT query

`SELECT     fldAppStart AS StarttimeFROM         tblAppointmentsWHERE     (fldPrimaryAppDate = CONVERT(DATETIME, '2013-03-17 00:00:00', 102)) AND (fldStudioNo = '103')`

That returns the values

 480 720 600 810 810 630

OK, so when I add /60 to the SELECT query above ie:

```SELECT     fldAppStart/60 AS Starttime

FROM         tblAppointments

WHERE     (fldPrimaryAppDate = CONVERT(DATETIME, '2013-03-17 00:00:00', 102)) AND (fldStudioNo = '103')```

That returns the values

 8 12 10 13.5 13.5 10.5 So, I get the right look for times that fall on the hour (ie: 8, 10, 12), but return a .5 decimal for times that fall on the half hour (ie: 13.5, 10.5) because 810/60 = 13.5. So I just need the 13.5 to look like 13:30, and the 10.5 to look like 10:30 and so on... When I try ```SELECT CONVERT(time,(CONVERT(char(2), fldAppStart / 60) + ':' + RIGHT('0' + CONVERT(char(2),(fldAppStart % 60)),2))) as timeDT FROM tblAppointments WHERE (fldPrimaryAppDate = CONVERT(DATETIME, '2013-03-17 00:00:00', 102)) AND (fldStudioNo = '103')``` I get the message TITLE: Microsoft SQL Server Report Designer ------------------------------ An error occurred while executing the query. The data types real and int are incompatible in the modulo operator. ------------------------------ ADDITIONAL INFORMATION: The data types real and int are incompatible in the modulo operator. (Microsoft SQL Server, Error: 402) So unfortunately, I can't get the solutions you've suggested to work. I hope that makes a bit of sense.

• Saturday, March 16, 2013 4:23 PM

I should have asked what datatype your column was.  As your error suggests, modulo (the % operator I'm using to get the remainder minutes) requires exact datatypes for numerator and denominator.  If you're not familiar with this operator, more details in BOL here.

To resolve this, you just need to convert your column from REAL to an acceptable datatype for the modulo operation.  I've converted to INT below.

```declare @minutes real = 810
SELECT
CONVERT(time,
(STR(CONVERT(int,@minutes) / 60 ) )
+ ':'
+ RIGHT('0' + CONVERT(char(2),(CONVERT(int,@minutes) % 60)),2)
) as timeDT```

Also, I've included the variable again to provide a fully functioning query example with the exact value you provided.  As before, you should be able to take the expression in the SELECT statement and use it in your query once you've replace the variable (@minutes) with your actual column.

Hope that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

• Edited by Saturday, March 16, 2013 4:30 PM
• Marked As Answer by Sunday, March 17, 2013 2:39 AM
•
• Saturday, March 16, 2013 11:02 PM

Thanks Brent - that worked perfectly.