March 9, 2012 at 8:18 am
Hi Everybody,
My source is flat file has column1 which is string type and values are like "12/21/2010" i.e., in MM/DD/YYYY format.
I need to load this value as 2010-12-21 i'e.,in YYYY-MM-DD format to destination flat file.
I am using below code in Derived component to convert ,
[ORDERDATE] == "" ? NULL(DT_DATE) : (DT_DATE)[ORDERDATE]
When I view the data using data viewer it looks like "2010-09-23 00:00:00.0000000".
But after loading to destination file,In destination file data is same as source ("12/21/2010").
The destination column data type is String of size 50.I also tried by changing the type to DT_DATE but no use.
Any Help would be greatly appreciated.
Regards,
Sunny
March 12, 2012 at 12:49 am
Maybe SSIS is using regional settings to determine the date format when writing to the destination flat file.
I would use the DT_STR datatype in SSIS (string datatype), convert the date to the format you want and write it as text to the flat file. That way you're in full control.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 12, 2012 at 3:38 pm
Try this: [ORDERDATE] == "" ? DT_DATE : RIGHT([ORDERDATE],4) + "-" + SUBSTRING( ORDERDATE, 4, 2 ) + "-" + SUBSTRING( ORDERDATE,1,2)
March 13, 2012 at 12:04 am
Dima-99208 (3/12/2012)
Try this: [ORDERDATE] == "" ? DT_DATE : RIGHT([ORDERDATE],4) + "-" + SUBSTRING( ORDERDATE, 4, 2 ) + "-" + SUBSTRING( ORDERDATE,1,2)
There's a small error here:
[ORDERDATE] == "" ? DT_DATE : ...
That part doesn't actually do anything and in order for a cast to work, you need to enclose it into brackets.
(DT_DATE) myDateString
Furthermore, if it is a cast to DT_DATE, the "true" part is of datatype DT_DATE and the "false" part is a string and those datatypes don't match.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply