Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Convert the number 13.50 to 1:30pm

Answered Convert the number 13.50 to 1:30pm

  • Thursday, March 14, 2013 11:54 PM
     
      Has Code
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    1

    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


     
  • Friday, March 15, 2013 2:13 AM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



     
  • Friday, March 15, 2013 7:09 AM
     
     
    Sign In to Vote
    0
    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
     
     
    Sign In to Vote
    0

    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


     
  • Friday, March 15, 2013 4:48 PM
     
     
    Sign In to Vote
    1

    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
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

     
  • Friday, March 15, 2013 4:56 PM
     
     
    Sign In to Vote
    0

    Hi,

    Try the following please :

    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
     
      Has Code
    Sign In to Vote
    0

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

    SELECT     fldAppStart AS Starttime
    FROM         tblAppointments
    WHERE     (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
     
     Answered Has Code
    Sign In to Vote
    1

    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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    • Edited by Brent Greenwood Saturday, March 16, 2013 4:30 PM
    • Marked As Answer by jdfogarty9 Sunday, March 17, 2013 2:39 AM
    •  
     
  • Saturday, March 16, 2013 11:02 PM
     
     
    Sign In to Vote
    0

    Thanks Brent - that worked perfectly.

    Appreciate your help (and especially your patience...) on this one

    Cheers

    John

     
  • Sunday, March 17, 2013 1:35 AM
     
     
    Sign In to Vote
    0

    No worries John.  Glad to hear you got it sorted.  Please remember to mark correct answers and helpful posts.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com