Extract time from date into new column I have a varchar column named [Dato Time] which contains values like:

2011-05-12 00
2011-05-12 01
2011-05-12 02


So, a date and a Hour-specific time.

I would like to split the date and the time up in two columns, a column named [Dato] with datatype date and a column named Time with datatype time(7).

I extracted the date-part with the following substring:

UPDATE Dato_Time SET [Dato] = SUBSTRING([Dato Time], 1, 10)


This worked without any problem. But when I try to run the same code for the time-part:

UPDATE Dato_Time SET [Time] = SUBSTRING([Dato Time], 12, 13)


It gives me the following error:


  Conversion failed when converting date and/or time from character string.


Any ideas?
Since Char 12 and 13 seem to be hours, with SQL Server 2012, you can build a new time using this part and 0 for missing parts:


  TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )


Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0) 


MSDN: TIMEFROMPARTS (Transact-SQL)

With SQL Server prior to 2012, you can use such queries:

Select DATEADD(HOUR, Cast(SUBSTRING(@x, 12, 13) as int), CAST('00:00' as time))
Select Cast(SUBSTRING(@x, 12, 13)+':00' as time)