Adam Machanic is hosting this month’s T-SQL Tuesday (the first!) on Date/Time Tricks. I thought I’d share a couple datetime SSIS Expressions I use regularly to format dates.
First, I created an SSIS solution named ExpressionsTester, to which I added a data flow transformation, to which I added an OLEDB Source Adapter, Derived Column Transformation, and Trash Destination Adapter. I also created a Connection Manager aimed at AdventureWorks. I don’t use the Connection Manager for anything – I simply need a connection manager to configure the OLEDB Source Adapter (someone needs to write a Trash Connection Manager…).
When configured, it looks like this:
The OLEDB Source (named “Now Source”) is configured as shown:
The query is:
Select getdate() as Now
— Select ‘1/1/1972’ as Now
Inside the Derived Colum Transformation (named Derive DateTime Stuff) I create a couple columns based on the value of the Now column supplied by the Now Source source adapter. They are named StrDateOnly and StrLeadingZeroDateOnly:
The expression for StrDateOnly is:
(DT_STR,10,1252)((DT_STR,2,1252)MONTH(Now) + “/” + (DT_STR,2,1252)DAY(Now) + “/” + (DT_STR,4,1252)YEAR(Now))
The expression for StrLeadingZeroDateOnly is:
(DT_STR,10,1252)(((MONTH(Now) < 10 ? “0” + (DT_STR,1,1252)MONTH(Now) : (DT_Str,2,1252)MONTH(Now))) + “/” + ((DAY(Now) < 10 ? “0” + (DT_STR,1,1252)DAY(Now) : (DT_Str,2,1252)DAY(Now))) + “/” + (DT_Str,4,1252)YEAR(Now))
Executing today (12/8/2009) produces the following output:
Note that both outputs are of the String data type (DT_STR in SSIS). This translates to the varchar data type in SQL Server, but can be cast / converted as needed. Also note the StrLeadingZeroDateOnly places a leading zero before the day (08) while StrDateOnly does not – and that’s the only real difference between the two expressions.
I can hear you thinking: “Andy, why didn’t you simple convert this in T-SQL?” I’m glad you asked! In this example, my source happens to be an OLEDB adapter, ostensibly connected to a relational database. This is not always the case. In SSIS, we regularly load data from flat files, XML, or custom sources that don’t have a handy (or simple) way to coerce data types.
:{> Andy
nice