user7172167 user7172167 - 10 months ago 64
SQL Question

Converting hours into minutes in SQL

i'm trying to write a regular expression that will convert the hours into minutes.
Currently I have a value which give me the number of hours before the dot and the number of minutes after the dot, for example 6.10 is 6 hours and 10 minute.

Can i use a regular expression to look for the value before the dot and multiply by 60 (to get minutes) and then add the value after the dot (which is already in minutes)?

So as per the example used before, it would do:
(6 * 60) + 10

Thank you!

Answer Source

Regular expressions is probably an overkill for such a simple string manipulation. All you need is INDEX function to get the location of the dot and SUBSTR function to get hours and minutes.

Dot position:

 select INDEX([yourstring], '.') 

Hours (before the dot):

 select SUBSTR([yourstring], 1, INDEX([yourstring], '.')  - 1)

Minutes (after the dot):

 select SUBSTR([yourstring], INDEX([yourstring], '.') + 1)