PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 05/28/2015 7:32 AM by  thummel1
Display numeric as time
 4 Replies
Sort:
You are not authorized to post a reply.
Author Messages
thummel1
Data Analyst II
Fairview Health Services
Veteran Member
(183 points)
Veteran Member
Posts:101


Send Message:

--
05/26/2015 12:53 PM
    I am working on a smart note. There are 2 numeric columns designed to display in/out punches that will eventually come over in the Smart Note. One column has 'in' punches and the other has 'out' punches. When the table was designed, these columns were not formatted like hh:mm. They were formatted as numbers, no decimals. So the 'in' punch will look like 701 and the 'out' punch will look like 1150 (example).

    Question: The end user wants to view these numbers like hh:mm. I am trying to think of creative ways to do that.
    Idea 1: convert numeric to hh:mm, but everything I look up doesn't support this particular type of translation
    Idea 2: formula to add a colon two digits to the left. I think thi swoudl work but just not sure how to construct the formula?

    If you have any suggestions formulas you can direct me to that would be great. Thank you!
    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    05/26/2015 2:18 PM
    Assuming you are using Oracle SQL for the smart note:

    to_char(to_date(to_char(your_timestamp, '0000'),'HH24MI'),'HH24:MI')

    This converts the numeric to a four digit right zero padded string and then converts that back to a datetime value. It then reconverts the datetime back into a character string in 24 hour format with a : between the hour and the minutes.

    I'm making assumptions that your SQL environment doesn't have a built in time only datatype (for instance Oracle has a timestamp datatype, however it is actually a datetime value with added precision on the seconds) if you did, you might be able to cut out the outer to_char and let SQL format the output on it's own.

    But lacking that, if you attempted to run the above without manually specifying the format to use for the output, you'd get something more along the lines of '01-May-15' without any reference to a time.
    thummel1
    Data Analyst II
    Fairview Health Services
    Veteran Member
    (183 points)
    Veteran Member
    Posts:101


    Send Message:

    --
    05/26/2015 3:06 PM
    Thank you for your reply. I've inserted this formula into the statement. I've tried the following iterations:
    TO_CHAR(to_date(TO_CHAR(ZZTMEDTTRN.IN_PUNCH, '0000'), 'HH24MI'), 'HH24:MI') -Error: ORA-01722: invalid number
    TO_CHAR(to_date(TO_CHAR(.IN_PUNCH, '0000'), 'HH24MI'), 'HH24:MI') -Error: ORA-01722: invalid number
    to_date(TO_CHAR(ZZTMEDTTRN.IN_PUNCH, '0000'), 'HH24MI'), 'HH24:MI') -Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

    Not sure where to go with these errors, any thoughts are welcome.

    Another thought....If this is a numeric field as my table suggests, what about dividing by 100 and then replacing the decimal with a colon?
    Again, not sure how to construct that, but it was another thought I had. I could do those things in Access or Crystal, but I'm not as versed in SQL Developer.
    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    05/26/2015 4:08 PM
    Unless that third version is missing text, your value doesn't seem to be the numeric you thought it was.

    It may already be a datetime or it could be a character string already.

    The first error, the only function that is expecting a number is the innermost TO_CHAR. And it's complaining that your timestamp isn't an number. The second is just a repeat. The third however is complaining that you are passing it a datetime when it wants a number. to_date doesn't want numbers, it wants dates. So the only thing that could be complaining there is to_char.

    Try just
    TO_CHAR(zztmedttrn.in_punch,'HH24:MI') - assuming it's a date
    or
    to_char(to_date(lpad(your_timestamp,4, '0'),'HH24MI'),'HH24:MI') - assuming it's text.

    or alternatively, since you mention you have SQL Developer installed pull up the table and on the columns tab, what is the datatype the field is defined as?
    thummel1
    Data Analyst II
    Fairview Health Services
    Veteran Member
    (183 points)
    Veteran Member
    Posts:101


    Send Message:

    --
    05/28/2015 7:32 AM
    Hi All,

    I was able to get a code that works. I am sharing it below:
    to_char("ZZTMEDTTRN"."IN_PUNCH",'fm00G00','nls_numeric_characters=,:') as "IN_PUNCH",
    The code above changes 1030 to 10:30 and 430 to 04:30.
    Thank you to all who helped!
    You are not authorized to post a reply.