sql server - Convert varchar 2007-12-19-11.57.17.366731 to datetime datatype -

i have data shown below

2007-12-19-11.57.17.366731  

and datatype varchar(26)

i want change datatype datetime.

i tried syntax no use , getting

the conversion of varchar data type datetime data type resulted in out-of-range value.

here sample trying

select convert(datetime, '2007-12-19-11.57.17.366731', 120) 

thanks

well, first of all, you'll need add string magic transform data accepeted format datetime. , need use less miliseconds (for datetime have 3 digits it), in sql server 2008 can use datetime2 this. try following:

declare @value varchar(26) set @value = '2007-12-19-11.57.17.366731'  select  convert(datetime,left(@value,10) + ' ' +                           replace(substring(@value,12,8),'.',':') + '.' +                          substring(@value,21,3), 120) valueasdatetime,         convert(datetime2,left(@value,10) + ' ' +                           replace(substring(@value,12,8),'.',':') + '.' +                          right(@value,6), 120) valueasdatetime2 

results:

╔═════════════════════════╦═════════════════════════════╗ ║     valueasdatetime     ║      valueasdatetime2       ║ ╠═════════════════════════╬═════════════════════════════╣ ║ 2007-12-19 11:57:17.367 ║ 2007-12-19 11:57:17.3667310 ║ ╚═════════════════════════╩═════════════════════════════╝