December 19, 2013 at 12:31 am
Hi, i am new to SSIS.I learning SSIS,by creating some sample packages.I am loading data from .csv file to SQL server table.In csv file i have one column as varchar format (eg:22Feb2012),but i need to convert into date format as YYYY-MM-DD.Can you help me in creating Expression for this conversion.
Let me know if you need further details.
December 19, 2013 at 12:50 am
shalini.doss (12/19/2013)
Let me know if you need further details.
Is 22Feb2012 the exact format? For single digit days, is it 01 or 1?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 19, 2013 at 10:17 am
It is 01 for single digit
December 19, 2013 at 1:41 pm
It should be similar to this:
(DT_Date)(SUBSTRING("01Feb2012",3,3) + " " + SUBSTRING( "01Feb2012",1,2) + ", " + SUBSTRING( "01Feb2012",6,4))
You may have to tweak it a bit, but it should look like "Feb 01, 2012" to the converter.
I don't have time at the moment to create a full test for this, as date conversion can get a bit funky but that's the general idea. If you can't get this to work for you with a bit of tweaking post back and I'll setup an end to end on it.
This expression works in a quick text field test I did:
(DT_WSTR,4000)((DT_Date)(SUBSTRING("01Feb2012",3,3) + " " + SUBSTRING( "01Feb2012",1,2) + ", " + SUBSTRING( "01Feb2012",6,4)))
So I'm pretty confident in it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 22, 2013 at 7:58 am
Thanks for the reply,I forgot to mentioned one thing,my source date format is 22-Feb-13.i need to convert this to YYYY-MM-DD.
Sorry i should have mentioned this correctly first.
December 23, 2013 at 1:17 am
shalini.doss (12/22/2013)
Thanks for the reply,I forgot to mentioned one thing,my source date format is 22-Feb-13.i need to convert this to YYYY-MM-DD.Sorry i should have mentioned this correctly first.
You should still be able to use Craig's solution, but adjusting the subscripts accordingly to allow for the extra characters.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply