SSIS Convert between datetimeoffset and datetime

The Problem

I had a sql table that was storing data in a DateTimeOffset(7) datatype, DateDeleted.

I was trying to use SSIS to sync the data to another table where the DateDelted column was a DATETIME datatype.

How do you cast/convert between a DateTimeOffset(7) datatype to a DateTime object

The Solution

In a derived column object use the following formula

DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))

I will walk you through what this actually is.

Part 1 – Base Cast


This is just a standard type conversion.
The problem!!

When you try this you will receive a “Truncation Error”.

  1. Open the derived column transformation editor
  2. Click on “Configure Error Output”
  3. On this page for your derived column (in my case C_DateDeleted), set the Truncation value to “Ignore failure”

SSISConfigureError output

So now it will work, but the problem is the offset is lost. In my case I am in Australia, +10 UTC.

Part 2 – Add the Offset back

So I want to add 10 hours back so I changed my derived column to

DATEADD("hh", 10, ((DT_DBTIMESTAMP)DateDeleted))

This works great, but what about daylight saving time when the offset may alter to 11 hours etc.

Part 3 – calculate the offset

DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted)

This says get the hours difference between that DateTime version of the date ((DT_DBTIMESTAMP)DateDeleted) and the DateTimeOffset version of the date.

Part 4 – Final version

Now put it all together

DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))

Easy :-)… not!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.