DayLight Saving in Derived Column

Question

Hi All,

 

I am receiving some orders as an XML. Those orders need to be loaded to SQL Server.

The order date is in GMT. I need to convert it to EST and handle DayLight Saving too.

Is it possible to do using the existing functions in SSIS?

 

Else, i guess i will have to create a procedure to update the dates once they get loaded.

Or let me know if there is any other method.

Thanks!

Answer 1

There are no stock (existing functions to handle the GMT time and/or Daylight savings). Reason is it is very much dependent on the local settings.

For example some timezones are having 30 min time intervals. Some places do not honor the daylight saving rules, and many are opting out of this program I think this year.

To suggest you on how to update the dates, you better post here how the records are flowing in. Example: in one of my ETLs I have a XML in which the date has a timezone indicator: 

<TimeStamp2>1976-02-18T17:52:06</TimeStamp2>

Is that the case with your request, too?

---

Arthur My Blog

Answer 2

One thing you could do is pump the rows through a script component and do the conversions.  The C# .NET code to do it would be:

 TimeZoneInfo timeZoneGMT;
 TimeZoneInfo timeZoneEST;
 DateTime Converted_dateTime;

 timeZoneGMT = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");
 timeZoneEST = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
 Converted_dateTime = TimeZoneInfo.ConvertTime(DateTimeFromXML, timeZoneGMT, timeZoneEST);

 

---

Tom Overton

Answer 3

I would like to recommend Tom's solution. It will work , including handling DST. Just keep in mind you have to have .NET 3.5 installed for this code to work.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/