asked
how to convert cyymmdd in oracle to datetime in sql using ssis 2005
I have a date column in oracle database in format CYYMMDD where the first byte is the century (1=20, 0=19) and another column (time) i want to put it in sql table in format 2012-12-10 14:00:00.000 (date time) i'm using ssis 2005 and can't manage to do it please help thanks
Dump the raw data into a staging table using a char(7) column then use a case statement on the first character to prepend the century to a datetime string. Concatenate the remaining characters plus '14:00:00.000' for the time and convert the whole mess to datetime: declare @oracledate char(7); set @oracledate = '1121210'; select convert(datetime, case when left(@oracledate, 1) = '1' then '20' else '19' end + substring(@oracledate, 2, len(@oracledate)) + ' 14:00:00.000');
I have a string formatted as 2011-11-10 14:30:30.000 and I want to insert it in sql table datetime column. I’m using derived column in ssis 2005 How shall I write the expression please
Didn't like the staging table approach? Books Online suggests that the `CAST` expression in SSIS can change the data type from string to datetime -
http://technet.microsoft.com/en-us/library/ms141704.aspx you might try the db_timestamp example
More detail would be helpful. Are you testing for NULL values? Are you getting any errors? The SSIS team at Microsoft have a quick string to date conversion demo -
http://blogs.msdn.com/b/mattm/archive/2010/08/04/string-to-date-conversion.aspx Here's a quick idea for troubleshooting the conversion -
http://consultingblogs.emc.com/jamiethomson/archive/2006/06/26/SSIS_3A00_-Parsing-datetime-values.aspx
15 People are following this question.
