January 13, 2009 at 4:01 pm
Hi guys,
i need your suggestions about converting a date field. The source is csv flat where ssis loads the date column as "database date [DT_DBDATE]", later i convert the result into 3 fields using direved columns data flow. it looks this ways:
Within the Derived Column transformation Editor:
Derived Column Name derived column Expression DataType
Year add as new column YEAR(Year) string[DT_STR]
Month add as new column MONTH(Month) string[DT_STR]
Day add as new column DAY(Day) string[DT_STR]
then all the data goes into a sql db.
Now the problem are Days like "02" that are written as "2", Month like "05" = "5"....i want to keep the date correctly so when
the date is 02.09.2008 the sql fields must get the "0" too:
Year Month Day
2008 09 02
Any idea??....
January 13, 2009 at 4:18 pm
Take a gander at this link
http://www.ssistalk.com/2007/03/30/ssis-some-random-tidbits/
(DT_WSTR,4)YEAR(GETDATE())
RIGHT(”00? + (DT_WSTR,2)MONTH(getdate()),2)
RIGHT(”00? + (DT_WSTR,2)DAY(getdate()),2)
January 13, 2009 at 4:38 pm
thanks for this!
where in the flow do i insert this command:
(DT_WSTR,4)YEAR(GETDATE())
RIGHT(”00? + (DT_WSTR,2)MONTH(getdate()),2)
RIGHT(”00? + (DT_WSTR,2)DAY(getdate()),2)
....sorry i´m newbie to sql!
January 13, 2009 at 4:53 pm
You need to replace the expression in Derived colums with what Ray has suggested.
Derived Column Name Expression
Year (DT_WSTR,4)YEAR(GETDATE())
Month RIGHT(”00? + (DT_WSTR,2)MONTH(getdate()),2)
Day RIGHT(”00? + (DT_WSTR,2)DAY(getdate()),2)
January 13, 2009 at 4:53 pm
sorry misunderstood.....it´s fine and works! thanks for you this....
January 14, 2009 at 4:19 am
You have a database where you store year, month, and day as separate columns? Oooohhhh.... duuude! {shaking head in pity} 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply