Hi,
I am importing (using DTS) dates from an ODBC data source which come across as char (6) in the format A21125 for 25th November, 2002. What is the best way to get convert this data type into a meaningful date in SQL Server 2000? I tried doing a string operation as follows:
select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED,
convert(datetime,'200' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))
from FACTORD_DTL
and got the following error mesage:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Without the CONVERT I get 2002-11-23 varchar(10). How do a convert this output to a date?
I am importing (using DTS) dates from an ODBC data source which come across as char (6) in the format A21125 for 25th November, 2002. What is the best way to get convert this data type into a meaningful date in SQL Server 2000? I tried doing a string operation as follows:
select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED,
convert(datetime,'200' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))
from FACTORD_DTL
and got the following error mesage:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Without the CONVERT I get 2002-11-23 varchar(10). How do a convert this output to a date?