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
[sql]
DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))
[/sql]

I will walk you through what this actually is.

Part 1 – Base Cast

[sql]
(DT_DBTIMESTAMP) DateDeleted
[/sql]
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”

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

[sql]
DATEADD("hh", 10, ((DT_DBTIMESTAMP)DateDeleted))
[/sql]
This works great, but what about daylight saving time when the offset may alter to 11 hours etc.

Part 3 – calculate the offset

[sql]
DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted)
[/sql]
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

[sql]
DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))
[/sql]

Easy :-)… not!!!