Menu
I am having a table in SQL Server where there is a column name TimeSpent Datatype Varchar(25). It basically stores Time as HH:MM Format.
As per the requirement now i want that it gives me actual timespent in Minutes i.e. 01:00 gives me 60, 01:30 -> 90.
Please help me to write the query in SQL Server 2008 , so that will convert Varchar (HH:MM) into Minutes(integer).
302
Assuming you are looking for the "time" analogy to the "date" portion of your code which takes YYYYMMDD and turns it into an INT , you can: start with the HH:mm:ss format given by the style number 108. remove the colons to get that string into HHmmss. then convert that to INT.
To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.
Try
SELECT LTRIM(DATEDIFF(MINUTE, 0, TimeSpent))
FROM yourtable
Given
| TIMESPENT |
-------------
| 00:12 |
| 01:05 |
| 10:00 |
Output:
| MINUTES |
-----------
| 12 |
| 65 |
| 600 |
Here is SQLFiddle
89
This will solve your issue. Please refer to SQLFiddle
select cast(left(TimeSpent, charindex(':',TimeSpent,0)-1) as int)*60+
cast(right(TimeSpent, len(TimeSpent)-charindex(':',TimeSpent, 0)) as int)
from test
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
