0

I am working with a table that already exists that has columns formatted in some sort of a minute format. It looks like it was meant to be hh:mm, but it is not quite. For instance, where it should be 03:00, it says 01:80 for 180 minutes. Or for 1:30 it states 00:90. How can I convert these columns to an hh:mm format? Any help would be great, I'm struggling! Thanks!

eatonphil
  • 13,115
  • 27
  • 76
  • 133
  • I'm not sure you've given enough information to answer the question. How would your program know if 1:40 meant 100 minutes or 140 minutes? Is at string of form "01:80" stored in the database or is something else stored in the database then formatted later? You can try Select %INTERNAL(MyTimeColumn) to see the internal format. – psr Aug 30 '12 at 22:11
  • It is stored as a string like you said: '01:80'. Which means three hours. – eatonphil Aug 31 '12 at 20:22

1 Answers1

1

The following elegant code:

SELECT ((myField*100)+RIGHT(myField,2))/60 
         || ':' 
         || RIGHT('00'(((myField*100)+RIGHT(myField,2))#60),2) 
From myTable

seems to work.

psr
  • 2,870
  • 18
  • 22