SQL Convert & Cast Nvarchar Time to a decimal-CodePudding

I'm working on a legacy database and need to parse info from one database to another, parsing it into the new database is easy enough but first I need to create the query to convert and cast the following in the legacy SQL Server database:

WorkedHours(NVARCHAR(10)) is in text format 07:30

I need to convert and cast this as a decimal ie 7.5

I have searched around for the answer to this but can not find anything that has worked, so thought I would put it out there to see if any of you has any ideas.

Edit - What I should of asked is. What is causing an error converting to an int from a character with a value of 0 when trying to trying to convert and cast a time to a decimal?

CodePudding user response：

DATEDIFF(
  MINUTE,
  0,
  CAST('07:30' AS TIME)
)
/
60.0

Works up to '23:59' only

EDIT:

Based on a comment elsewhere, you have some 'bad' values.

This may find them...

SELECT
  *
FROM
  yourTable
WHERE
  TRY_CONVERT(TIME, worked_hours) IS NULL

And as such, this is a safer version of my expression....

DATEDIFF(
  MINUTE,
  0,
  TRY_CONVERT(TIME, worked_hours)
)
/
60.0

(Returns NULL for values that failed to parse.)

CodePudding user response：

There's no reason to pull out the date/time types. Just do some simple string parsing:

cast(left(right('0'   WorkedHours, 5), 2) as int)
    cast(right(WorkedHours, 2) as int) / 60.00

This won't have any limitations on 24 hours or anything like that. It just assumes that you've got one or two digits before a colon and two digits after.

CodePudding user response：

This should work in SQL Server and an example-string "1101:56" (1101h & 56 minutes) | in general from 0h to >24h:

-- Take all hours before ":" and all Minutes (2 digits) after ":" and convert it to decimal.
select convert(decimal,left('1101:56',CHARINDEX(':','1101:56')-1))   ( convert(decimal,right('1101:56',2))/60 );

-- with column-placeholder "time_str_from_table"
select convert(decimal,left(time_str_from_table,CHARINDEX(':',time_str_from_table)-1))   ( convert(decimal,right(time_str_from_table,2))/60 );

If the source table have NULL-Values, than use "ISNULL" with substitution-value "0.0":

-- with column-placeholder "time_str_from_table"
select isnull( ( convert(decimal,left(time_str_from_table,CHARINDEX(':',time_str_from_table)-1))   ( convert(decimal,right(time_str_from_table,2))/60) ), 0.0);