Convert varchar to Datetime

[jxc]

I have a value in a view which I need to convert to a date and time datatype

eq: 199912102359

I have tried the convert function but without success.

Thanks in advance

 

[MeanGreen]

Try this where 'value' is your string:

select convert(datetime,left(value,4) + '/' + substring(value,5,2) + '/' + substring(value,7,2) + ' ' + substring(value,9,2) + ':' + substring(value,11,2),101)

Hope this helps.

 

[SonOfEmidec1100]

Not real neat but it works

Declare @string char(12),@MyDate datetime
select @string = '199912102359'
select @MyDate = convert(datetime,substring(@string,1,8)) + convert(datetime,substring(@string,9,2) + ':' + substring(@string,11,2))

cjw

 

[tlbroadbent]

One more variation.

Select DateTimeValue=convert(datetime, left(YourCol,8)) +
convert(datetime, stuff(right(YourCol,4), 3,0,':'), 8)
From YourTable Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.