CODEWITHSUNDEEP
sqlsql-server
jason
jason

Reputation: 7164

SQL conversion from varchar to datetime

One of the columns of my SQL Server table is mm:ss of varchar type where mm = minutes and ss = seconds.

I need to get the average of that column.

Should I convert that column to datetime format first? If so can you tell me how? If not can you tell me what I should do?

Here is my failed attempt to convert it to datetime : 

SELECT CONVERT(DATETIME, '2014-01-01 '+Pace+':00', 108)

Where pace is a varchar like 23:05

Upvotes: 0

Views: 226

Answers (3)

Anon
Anon

Reputation: 10908

For SQL2012 and later

SELECT
  FORMAT(DATEADD(second,AVG(DATEDIFF(second,0,'00:'+[Pace])),0),'mm:ss')
FROM MyTable

Upvotes: 1

Fernando Sibaja
Fernando Sibaja

Reputation: 127

declare @pace varchar(20) = '23:05                    ';

SELECT cast( '2014-01-01 '+cast(@pace as varchar(5))+':00' as datetime)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you want the average, I would convert the column to number of seconds and work with that:

select avg(pace_secs) as average_in_seconds
from (select cast(left(pace, 2) as int) * 60 + cast(right(pace, 2) as int) as pace_secs
      from t
     ) t;

If you want this back in the format, then you can do:

select right('00' + cast(avg(pace_secs) / 60 as int), 2) + ':' +
       right('00' + avg(page_secs) % 60), 2)
    from (select cast(left(pace, 2) as int) * 60 + cast(right(pace, 2) as int) as pace_secs
          from t
         ) t;

Upvotes: 3

Related Questions