August 11, 2009 at 9:37 am
I have a column in flat file which stores date. The data is 6 character in lengh, so it looks like
090803
070812
090725
071108
My sql table where i insert this data is smalldatetime.
I need to convert this to smalldatetime before I insert. I know, there is no simple way like T-SQL.
Can someone help me to use derived column or data conversation to convert this. Also making sure that if it is empty string i just want null values to be inserted, because i know if we try to convert empty string to smalldatetime it gives error
all helps are appreciated
August 11, 2009 at 9:41 am
select convert(smalldatetime, '090803')
OUTPUT
--------
2009-08-03 00:00:00
(1 row(s) affected)
Is this what you need?
also if you paas empty string, it wont give error, instead, it'd return 1900-01-01 00:00:00
August 11, 2009 at 9:54 am
ps thanks...But looks like i confused u. I know I can easily do in T-SQL.
But the problem is i need to do this inside the dataflow task in the pipelines.
The one way I can think of doing this is issue a OLE DB command on each row. But i want to avoid this as it is very costly.
I know there is a way to do with Derived column ( i did one a long time ago).
You have to build expression. I tired all my possible options, still fails.
Either you can use derived column or conversation task to build expression to nicely hendle these.
In ssis it doesn't understand that the field is date, and so it expects the data to be in ceratin format to understand that it is a date field.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply