Incorrect Date Format

I am moving data from an Interbase DB to SQL Server 2005. The date
columns for whatever reason in Interbase are not valid dates and they
have vChar field types. This is how they are formatted
2006-03-13-00.02.04 notice the extra hyphen between the date and time.
I have created an SSIS package import and would like to correct the
date then. I have looked at but not figured out how to use the 'derived
column' function in SSIS.
Does anyone have any tips on how to do this. Or maybe an easier way.This seems to convert the string into a format that can be converted to
datetime. Hopefully you find it helpful.
DECLARE @.str varchar(50)
SET @.str = '2006-03-13-00.02.04'
SELECT SUBSTRING(@.str, 1,10) + ' ' + SUBSTRING(@.str,12,
(datalength(@.str)-11))
SELECT convert(datetime,REPLACE((SUBSTRING(@.str
, 1,10) + ' ' +
SUBSTRING(@.str,12, (datalength(@.str)-11))),'.', ':'))
Keith Kratochvil
"Skip" <mike_sylvester@.voughtaircraft.com> wrote in message
news:1144252421.220884.54000@.j33g2000cwa.googlegroups.com...
>I am moving data from an Interbase DB to SQL Server 2005. The date
> columns for whatever reason in Interbase are not valid dates and they
> have vChar field types. This is how they are formatted
> 2006-03-13-00.02.04 notice the extra hyphen between the date and time.
> I have created an SSIS package import and would like to correct the
> date then. I have looked at but not figured out how to use the 'derived
> column' function in SSIS.
> Does anyone have any tips on how to do this. Or maybe an easier way.
>|||Thanks. I have come up with a similar conversion, but used in SSIS as a
drived column function. It converts to a valid date minus adding AM or
PM. Problem is that I add data conversion to change it into a date
field it fails? Does it actually need AM or PM added to it? Almost
there...
Here is my version
REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")|||I do not think has has anything to do with AM or PM. I think the field
conversion generates the typical data conversion loss statement, and
that is what is failing the SSIS package. Any thoughts on this..|||As long as the data is in 24 hour format you should not need AM/PM. Do you
have the data that you want to convert to a datetime stored within a table?
Can you try running my T-SQL against the data to see if it converts
correctly?
Keith Kratochvil
"Skip" <mike_sylvester@.voughtaircraft.com> wrote in message
news:1144261325.341459.91850@.i40g2000cwc.googlegroups.com...
> Thanks. I have come up with a similar conversion, but used in SSIS as a
> drived column function. It converts to a valid date minus adding AM or
> PM. Problem is that I add data conversion to change it into a date
> field it fails? Does it actually need AM or PM added to it? Almost
> there...
> Here is my version
> REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
> RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")
>