September 6, 2017 at 2:45 pm
I have a comma delimited flat file as input to a package I am developing. It has a date column that looks like this 10/08/16
I want to convert it to a database date format. Here is my expression, but the leading 0 is not appearing in the month and/or day.(DT_DBDATE)((DT_STR,2,1252)"20" + (SUBSTRING(ServiceDate,7,2) + "-" + RIGHT(REPLICATE("0",2) + (DT_STR,2,1252)SUBSTRING(ServiceDate,1,2),2) + "-" + RIGHT(REPLICATE("0",2) + SUBSTRING(ServiceDate,4,2),2)))
Suggestions?
Thanks!
September 6, 2017 at 8:22 pm
I think your code is working as you need it to, it seems to convert "10/08/16" into a column of DT_DBDATE datatype, which be inserted into an SQL destination DATE column without problem.
Casting the result of the expression you've included to a string (or viewing it in a data viewer) displays it for me without the leading 0 as you're describing, but you don't need to do that to insert into a database DATE column.
If you do want the column in the locale-specific string format with the leading 0, I'm unaware of any CONVERT/FORMAT equivalent in SSIS - I would do something like the below, or string manipulation on the result of the expression you've given.
(MONTH([ServiceDateDTDATE]) < 10 ? "0" : "") + (DT_STR,10,1252)MONTH([ServiceDateDTDATE]) + "/" + (DT_STR,10,1252)DAY([ServiceDateDTDATE]) + "/" + (DT_STR,10,1252)YEAR([ServiceDateDTDATE])
Note that I'm not an expert in SSIS, so the above comes without warranty, and I'll be watching this thread myself 😉.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply