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 a Data Philosopher at Andy Leonard Consulting, 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 author of Managing Geeks - A Journey of Leading by Doing, and 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

Contact Me


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