September 19, 2013 at 10:41 am
hi all,
I never found the SSIS 2008 R2 section so I am posting this here since it really is of somewhat general interest:
how do I convert a date in string format into a string that can be converted to a date.
If my string were YYYY-MM-DD HH:MM:SS.000 the conversion goes well.
I know how to convert YYYYMMDD into the format above, however my date strings are M/D/YYYY and they are not even padded.
Do you know how to parse M/D/YYYY and get YYYY-MM-DD HH:MM:SS.000 with the syntax of SSIS?
I tried
ISNULL(DATE) ? NULL(DT_DBDATE) : (LEN(DATE) == 0 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,2),4) + "-" + SUBSTRING(TRIM(DATE),1,FINDSTRING("/",DATE,1) - 1) + "-" + SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,1),FINDSTRING("/",DATE,2) - 1)))
but there's something wrong and I can't find the mistake,
Thanks,
kowlasky
September 19, 2013 at 3:02 pm
axes2000 (9/19/2013)
hi all,I never found the SSIS 2008 R2 section so I am posting this here since it really is of somewhat general interest:
how do I convert a date in string format into a string that can be converted to a date.
If my string were YYYY-MM-DD HH:MM:SS.000 the conversion goes well.
I know how to convert YYYYMMDD into the format above, however my date strings are M/D/YYYY and they are not even padded.
Do you know how to parse M/D/YYYY and get YYYY-MM-DD HH:MM:SS.000 with the syntax of SSIS?
I tried
ISNULL(DATE) ? NULL(DT_DBDATE) : (LEN(DATE) == 0 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,2),4) + "-" + SUBSTRING(TRIM(DATE),1,FINDSTRING("/",DATE,1) - 1) + "-" + SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,1),FINDSTRING("/",DATE,2) - 1)))
Not clear if the overall goal is to get the conversion to a valid date/time format or if the goal is to get the string into a particular string format for a date. I found the following expression to take a M/D/YYYY string date and convert it to an acceptable date format in SSIS:
(DT_DATE)"3/4/2013"
It returns a date/time with the following display format: 3/4/2013 12:00:00 AM. The expression is accepted as giving a valid date within a date/time variable.
September 27, 2013 at 12:11 pm
Thank you very much,
kowalsky
September 27, 2013 at 1:09 pm
Glad you were able to get it working.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply