Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Derived Column Trasnformation for Data conversion Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 12:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 11:48 AM
Points: 3, Visits: 25
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.
Post #1524464
Posted Thursday, December 19, 2013 12:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:51 AM
Points: 5,986, Visits: 6,930
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?



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1524470
Posted Thursday, December 19, 2013 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 11:48 AM
Points: 3, Visits: 25
It is 01 for single digit
Post #1524703
Posted Thursday, December 19, 2013 1:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:51 AM
Points: 5,986, Visits: 6,930
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1524782
Posted Sunday, December 22, 2013 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 11:48 AM
Points: 3, Visits: 25
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.
Post #1525356
Posted Monday, December 23, 2013 1:17 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 4,828, Visits: 11,177
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1525429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse