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 special form of SQL. Any suggestions? Thanks in advance!

5 Years
Discussion Span
Last Post by iamthwee

UPDATE: It is in Intersystem's CacheSQL. It complies to all SQL-92 standards if that helps.


Not familiar with that dbms. I know you said you can't use functions, (even string functions? Yikes!), but have you considered just unloading your data into a flat file, making your changes there (programmatically?), then reloading your data? I don't know how realistic that is, as in data volume, but it's all I can think of.

Good luck!


Yeah do this with a scripting language. I doubt you have the functionality in SQL.

For eg PHP might be

   public function convertTime($var)
        $tm = "";

        $tm = $tm . substr($var, 0, 2);
        $tm = $tm .(":");
        $tm = $tm . substr($var, 2,4);

        $tmp = ltrim($tm, "0");

          echo $tmp;

The above would work with the exception of 00:00 times

Edited by iamthwee

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.