Convert CHAR to DATE

[strom99]

I have the following field process_datetime which looks like this...(20020617094637) it is a CHAR field, I need to convert this to a date field that looks like '2002-06-17' or '06/17/2002'. I've tried both CONVERT and CAST with no luck in getting it to work. Any ideas? Thanks much!!

==========
select
tbl1.ref_nbr,
convert(datetime,tbl1.process_datetime,120),
cast(tbl1.process_datetime AS datetime)
from
i12 tbl1

where
tbl1.ref_nbr like 'm000013'

===========

ERRORS:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

 

[maswien]

try this:


select
tbl1.ref_nbr,
cast(left(tbl1.process_datetime,8) AS datetime)
from
i12 tbl1

where
tbl1.ref_nbr like 'm000013'

 

[JayKusch]

This should help out a bit ...

declare @String Char(14)
declare @NewDate DateTime

SET @String = '20020617094637'

SET @newdate = CONVERT(DateTime,SUBSTRING(@String,1,8),110)

print convert(char,@NewDate,101)

Thanks

J. Kusch