CODEWITHSUNDEEP
sqlsql-servertime
Alexandria
Alexandria

Reputation: 183

Need to convert time into number using ms sql

I have a table called DATA which has these two columns:-

TaughtDistinct - varchar

ValueofTaught - numeric(2,2)

Taught distinct holds a time for example 07:30 but I need that in the ValueofTaught column as 7.5.

enter image description here

I just cant work it out, I have tried the below query:-

select * from CQData2

--Add temporary column TempValueOfTaught
alter table CQData2 add TempValueOfTaught Numeric(5,2)


--Update temporary column with values from ValueOfTaught
update CQData2 set TempValueOfTaught = ValueOfTaught


--Set ValueOfTaught to null
update CQData2 set ValueOfTaught = NULL


--change data type of ValueOfTaught to numeric
alter table CQData2 alter column ValueOfTaught NUMERIC(5,2)


--Ensure TempValueOfTaught is returning numeric values only
Select * from CQData2 where ISNUMERIC(TempValueOfTaught)=0


--Update ValueOfTaught using TempValueOfTaught values
update CQData2 set ValueOfTaught = Cast(TempValueOfTaught as numeric (5,2))

Upvotes: 3

Views: 147

Answers (2)

user__42
user__42

Reputation: 573

You could also use the new time-Datatype to be more flexible with your Format:

DECLARE @MyTime1 varchar(max) = '07:30'
DECLARE @MyTime2 varchar(max) = '7:30'
DECLARE @MyTime3 varchar(max) = '7:30:00'

SELECT @MyTime1 as style1 
      ,DATEPART(HOUR,CONVERT(time(0),@MyTime1))
       + DATEPART(MINUTE,CONVERT(time(0),@MyTime1))/CONVERT(real,60.0) AS [Hours1]
      ,@MyTime2 as style2, 
      ,DATEPART(HOUR,CONVERT(time(0),@MyTime2))
       + DATEPART(MINUTE,CONVERT(time(0),@MyTime2))/CONVERT(real,60.0) AS [Hours2]
      ,@MyTime3 as style3, 
      ,DATEPART(HOUR,CONVERT(time(0),@MyTime3))
       + DATEPART(MINUTE,CONVERT(time(0),@MyTime3))/CONVERT(real,60.0) AS [Hours3]

Result:

style1   Hours1   style2   Hours2   style3   Hours3
07:30    7,5      7:30     7,5      7:30:00  7,5

Upvotes: 2

LDMJoe
LDMJoe

Reputation: 1589

Assuming your data is consistent in format with your example (especially, the leading zero for single digit hours), here's a quick proof of concept...

DECLARE @MyTime varchar(max)

SET @MyTime = '07:30'

SELECT 
    @MyTime, 
    CONVERT(real, LEFT(@MyTime, 2)) + (CONVERT(real, RIGHT(@MyTime, 2)) / 60.0) AS [ValueOfTaught]

For the update...

UPDATE 
    CQData2 
SET 
    ValueofTaught = ROUND(CONVERT(real, LEFT(TaughtDistinct, 2)) + (CONVERT(real, RIGHT(TaughtDistinct, 2)) / 60.0), 2)
WHERE
    ValueofTaught IS NULL

Note that I changed the data type from numeric(2,2) to real in my code. The numeric data type with Precision and Scale both set to two will never be able to hold a value greater than or equal to one.

Here's an SQL Fiddle of the whole deal, including as much sample data as was visible in the OP.

Upvotes: 3

Related Questions