Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert varchar to Datetime

Status
Not open for further replies.

jxc

IS-IT--Management
Jan 11, 2001
17
CA
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
 
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.
 
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
 
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:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top