THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns and Managing Geeks - A Journey of Leading by Doing, and author of the Stairway to Integration Services.

T-SQL Tuesday #001: A Couple SSIS Date Expressions

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

Published Tuesday, December 08, 2009 12:26 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



kiran said:


March 3, 2015 2:51 AM

Leave a Comment


This Blog



Friend of Red Gate

My Company

Blog Roll

Check out the Linchpin People Blog...
Linchpin People Blog

Contact Me


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement