Changing the type from string to date mm/dd/yyyy

Question

Post reply

Changing the type from string to date mm/dd/yyyy

greengiant

SSC Eights!

Points: 961
More actions
June 18, 2009 at 9:59 am

#138809

In my SSIS package, I have a source file .csv. The dates in this file are string ie. 3/14/09, 12/4/09. I'm transferring into a table that defines this date as char(10).
In my transformation property of my DTS, I used have the inputformat and outputformat defined as type=string and value=mm/dd/yyyy. When the data moved from the csv into the table, the date on the table would be 03/14/2009 and 12/04/2009.
How do I accomplish this in SSIS? Do I do this with Derived Column?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

First off, why would you want to store a date as a character column?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

greengiant SSC Eights! Points: 961 More actions · Answer 2

greengiant

SSC Eights!

Points: 961

June 18, 2009 at 10:33 am

#1011925

The app will only read it as char.

greengiant SSC Eights! Points: 961 More actions · Answer 3

I figured out how to do it in sql. How do you do it in SSIS derived columns.

Input is 3/1/2009 Output is 03/01/2009

SELECT

CAST( RIGHT(POWER(10,2) + CAST(CONVERT(CHAR(2),DATEPART(M, xDate),101) AS INT), 2) AS CHAR(2))

+ '/' +

CAST( RIGHT(POWER(10,2) + CAST(CONVERT(CHAR(2),DATEPART(D, xDate),101) AS INT), 2) AS CHAR(2))

+ '/' +

CAST( RIGHT(POWER(10,4) + CAST(CONVERT(CHAR(4),DATEPART(YYYY, xDate),101) AS INT), 4) AS CHAR(4))

FROM XYZ

Mukti SSCrazy Points: 2826 More actions · Answer 4

Try this:

Replace @[User::xDate] in the following expression with your column name ( i m using a variable just for example)

RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", (DT_DBDATE) @[User::xDate] ) ,2) + "/" + RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", (DT_DBDATE) @[User::xDate] ) ,2) + "/" + (DT_WSTR, 10) DATEPART( "yyyy", (DT_DBDATE) @[User::xDate] )

i am assuming the datatype of your coulm xDate is string type.

HTH

greengiant SSC Eights! Points: 961 More actions · Answer 5

I do not know how to do this. I took your string. Replaced the text User::xDate with xDate and placed it in as an expression in my derived column process. The derived column name is xDate (comes from the source table; passing the value to a destination table).

RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", (DT_DBDATE) @[xDate] ) ,2) + "/" + RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", (DT_DBDATE) @[xDate] ) ,2) + "/" + (DT_WSTR, 10) DATEPART( "yyyy", (DT_DBDATE) @[xDate] )

greengiant SSC Eights! Points: 961 More actions · Answer 6

Thank you very much. This expression did work.

In the expression, instead of using xDate, I needed to use Column 2.

RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", (DT_DBDATE) [Column 2] ) ,2) + "/" +

RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", (DT_DBDATE) [Column 2] ) ,2) + "/" +

RIGHT("0000" + (DT_WSTR, 10) DATEPART( "yyyy", (DT_DBDATE) [Column 2] ) ,4)