SQL conversion from varchar to datetime

Question

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

The best thing to do would be to convert it to an `int [seconds]` column. can mm > 59 ? If its 0 padded do the math on `cast(left(fld,2) * 60 + right(fld,2) as int)` — Alex K., Aug 18 '14 at 14:58
possible duplicate of [Convert varchar into datetime in SQL Server](http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server) — Orlando Herrera, Aug 18 '14 at 15:04
Please stop using VARCHARs as [TIME](http://msdn.microsoft.com/en-us/library/bb677243.aspx)s. If you want to store data from a DATETIME in another format, consider a float - which can be casted to a DATETIME directly. DATETIMEs stored as VARCHARs are usually due to poor programming practices where someone wanted UI data stored in the database and was too lazy to handle the data in a native form. If you don't own the code (vendor code), shame on them! — SQLMason, Aug 18 '14 at 15:19
Further more, SQL server is designed for set based operations, RBAR string functions are slow and painful! You'll also have to validate your data as your cast to remove trailing spaces or invalid entries (99:13). Save yourself a lot of headaches and change the column to a TIME and validate the data as you fill. Please believe me that you'll appreciate this in the long run. — SQLMason, Aug 18 '14 at 15:22
You didn't mention what **version** of SQL Server you're using - if you're on a currently supported version (which is **2008** or newer), you should use the `TIME(n)` data type (since this really is **time only** - no date involved) — marc_s, Aug 18 '14 at 16:39

score 3 · Accepted Answer · answered Aug 18 '14 at 15:03

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;

score 1 · Answer 2 · edited Aug 18 '14 at 16:39

1

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

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

edited Aug 18 '14 at 16:39

marc_s

732,580
175
1,330
1,459

answered Aug 18 '14 at 14:59

Fernando Sibaja

137
11

Thanks but I got : Conversion failed when converting date and/or time from character string. – jason Aug 18 '14 at 15:01
Did you run the exact script? – Fernando Sibaja Aug 18 '14 at 15:02
What type is your [pace] column? – Fernando Sibaja Aug 18 '14 at 15:04
declare @pace varchar(20) = '23:05 '; SELECT cast( '2014-01-01 '+cast(@pace as varchar(5))+':00' as datetime) ---- – Fernando Sibaja Aug 18 '14 at 15:10
Just cast it to varchar(5) first – Fernando Sibaja Aug 18 '14 at 15:11
sounds like he has a data problem (leading or trailing spaces). This is why it's a bad idea to do this. – SQLMason Aug 18 '14 at 15:21
It gave this error : The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Because I have values like 26:06 or 33:51 – jason Aug 19 '14 at 07:38

score 1 · Answer 3 · answered Aug 18 '14 at 17:03

1

For SQL2012 and later

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

answered Aug 18 '14 at 17:03

Anon

10,660
1
29
31

SQL conversion from varchar to datetime

3 Answers3