convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
Loane
Loane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRING(WEDate, 3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns is
>in the format char(6) rather than datetime. The problem seems to be that the
>text file column is in the format 'ddmmyy'. When I use the CONVERT statement
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>