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_I4) ((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

10 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

  4. I know this is an old post but for me … very timely… Thank you very much… exactly what I needed!

    Comment by Bob McClellan — 27 May 2016 @ 6:41 pm | Reply

  5. Hi folks,
    Exists some how to create a expression for a yesterday, and if is a Monday, generate a date of friday?

    Comment by Rafael Lima - Brazil — 13 September 2016 @ 4:55 pm | Reply

    • Hi Rafael, it is like this: DATEPART(“dw”,GETDATE()) == 1 ? DATEADD(“d”,-3,GETDATE()) : DATEADD(“d”,-1,GETDATE())

      Comment by Vincent Rainardi — 13 September 2016 @ 6:58 pm | Reply

  6. Hi, I’m starting with SSIS and create two new columns (year and month) to excel that I import into BD SQL with “Derived Column”, but do not know how to express to fill me the year and the current month in the created columns, Thank you for the help that you may be able to provide.

    Comment by Alex — 23 September 2016 @ 4:10 pm | Reply

    • Hi Alex, the expression for the current month is MONTH(GETDATE()) and for the current year is YEAR(GETDATE())

      Comment by Vincent Rainardi — 23 September 2016 @ 6:36 pm | 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

Blog at WordPress.com.

%d bloggers like this: