Data Warehousing and Business Intelligence

26 May 2012

How to Convert Datetime to Int in SSIS

Filed under: SSIS — Vincent Rainardi @ 5:30 am

This usually happen if you need to insert Date Key into a fact table. You need do convert a date from the source table which has datetime data type to integer, i.e. from ‘2012-05-26 00:00:00’ to 20120526.

In T-SQL you can do this to convert datetime to int:

convert(int, replace( convert(varchar(10), @Date, 20) ,’-‘,”))

But in SSIS data flow, what do you do?

You need to use derived column like this:

The Derived Column transformation will add a new column to the workflow, which is the integer Date Key.

In SSIS we have the following date and string functions:

So to convert today’s date to integer, the idea is to get the year component, and the month component and the day component (as 3 different string), then concatenate them, then convert to integer.

So in the derived column we do these 3 components:


(DT_WSTR,4) YEAR(GETDATE())  ----- (1)

(DT_WSTR,2) MONTH(GETDATE())    ------ (2)

(DT_WSTR,2) DAY(GETDATE())   ------- (3)

But we need to remember that for November, the (2) output is 2 digit (11). But for May, the (2) output is 1 digit (5). So for 22nd May 2012 it would be 2012522 because May is single digit. So we need to pad it with zero like this:


RIGHT( "0" + (DT_WSTR,2) MONTH(GETDATE()) , 2 )

And the same with date: if the date is 1st to 9th, it will be a single digit. So 6th May 2012 will be 2012056, because 6th is a single digit. So we need to pad it with zero, like this:


RIGHT( "0" + (DT_WSTR,2) DAY(GETDATE()) , 2 )

And finally we combine those 3 components using “+” like this:


(DT_WSTR,4) YEAR(GETDATE()) + RIGHT( "0" + (DT_WSTR,2) MONTH(GETDATE()) , 2 ) + RIGHT( "0" + (DT_WSTR,2) DAY(GETDATE()) , 2 )

So we will get a string like this: “20120522”.

As the last step, we just need to convert it to integer, using (DT_I4), like this:


(DT_I4)((DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2))

In SSIS we have the following type cast operator available to us:

Here’s how it looks as an expression on the Derived Column window:

So once again the expression we need to convert today’s date (datetime) in Derived Column to integer is:


(DT_WSTR,4) YEAR(GETDATE()) + RIGHT( "0" + (DT_WSTR,2) MONTH(GETDATE()) , 2 ) + RIGHT( "0" + (DT_WSTR,2) DAY(GETDATE()) , 2 )

Hope this helps,

Vincent Rainardi 26/5/2012

About these ads

5 Comments »

  1. [...] See the complete article here. [...]

    Pingback by How to Populate a Fact Table using SSIS « Data Warehousing and Business Intelligence — 26 May 2012 @ 2:25 pm | Reply

  2. [...] part 1 (link) we have populated a fact table (FactHire) by reading the source table (Hire) and looking up 3 [...]

    Pingback by How to Populate a Fact Table using SSIS (part 2) « Data Warehousing and Business Intelligence — 26 May 2012 @ 9:47 pm | Reply

  3. As a workaround, could the addition of a new field be done as part of the OLEDB Source task, thus minimizing the code for the derived column task.

    SELECT CONVERT(INT,CONVERT(VARCHAR,GETDATE(),112)) AS DateKey

    Comment by tarunrodrigues — 26 July 2012 @ 7:22 am | Reply

    • Also using the convert function as part of the sql code, will eliminate the need to append 0 for numbers 1-9, to get it in YYYYMMDD format.

      Comment by tarunrodrigues — 26 July 2012 @ 7:24 am | Reply

    • Yes we can do the conversion on the SQL task Winston

      Comment by Vincent Rainardi — 26 July 2012 @ 8:42 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: