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!

It is similar to Oracle if that helps!

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!

Member Avatar for iamthwee

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.