Need to convert time to number using ms sql
I have a table called DATA that has these two columns: -
TaughtDistinct - varchar
ValueofTaught - numeric (2,2)
The report being taught contains a time like 07:30, but I need 7.5 in the ValueofTaught column.
I just can't handle it, I tried the following 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))
source to share
Assuming your data is formatted consistent with your example (especially, a leading zero for one-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]
To update ...
UPDATE
CQData2
SET
ValueofTaught = ROUND(CONVERT(real, LEFT(TaughtDistinct, 2)) + (CONVERT(real, RIGHT(TaughtDistinct, 2)) / 60.0), 2)
WHERE
ValueofTaught IS NULL
Please note that I changed the datatype from numeric(2,2)
to real
in my code. A numeric datatype with precision and scale set to two can never hold a value greater than or equal to one.
Here's the SQL Fiddle of the entire transaction including as many data samples as the OP was seeing.
source to share
You can also use the new 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
source to share