sqlcastingintersystems-cache

Convert four Digit number into hour format SQL

I have looked into Cast and Convert, but I cannot find a way to do this. I need to convert four digits into an hour format. For instance, 0800 would become 8:00 or 1530 would become 15:30. I cannot use functions, I'm using a InterSystem's CacheSQL. Any suggestions? Thanks in advance!

EDIT: If it is any more convenient, I can just divide the four digits by one hundred to get values like 15 from original 1500, or 8.30 from 0830. Does this make converting to hour:minute format easier?

Solution

  • For CacheSQL, you can do this:

    SELECT {fn TRIM(LEADING '0' FROM LEFT(col_name, 2) || ':' || RIGHT(col_name, 2)) }
FROM table_name