Date/Time field troubles from Informatica mapping

(0) Share

Report

Posted on by Community Member

Hi everyone. This might be an informatica issue but just in case it isn't, I thought I'd post here first. I am trying to map to my Dynamics Date/Time fields by concatenating my Date and Time fields in my Informatica Expression, then mapping the result to Dynamics. As an example, I have a date - "3/4/2010", and a Time - "10:30:00 AM". After concatenating with my expression, I get an output result - "03/04/2010 10:30:00.000000" which I'm then mapping to my Dynamics Date/Time field. The problem is the Date/Time combo always ends up mapped as 7 or 8 hours ahead such as -"3/4/2010 6:30 PM" Can anyone lead me down the right path? Thanks for any help!

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (1)

Alex Fun Wei Jie 33,628 on at

Like (0)

Report

Hi,

how did you map the result to the field ? using JS or etc.

spend some time to read through below blog, it basically explain the truths about a date time field.

www.powerobjects.com/.../crm-2011-truths-about-datetime

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

Hi,

Yes this is due to UTC conversion of datetime when it's saving to the database. So there will be always you get the differences because of that . Dynamics CRM when Populating UI it's get converted to the local datetime.

Please refer below article to make it clear.

community.dynamics.com/.../dynamics-crm-datetimes-the-last-word

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am mapping using Informatica Cloud. I understand that there can be differences in time zones but that doesn't explain how the time can be 7 OR 8 hours. That part doesn't make any sense to me. Here is a screenshot showing a 7 hour difference. I'll keep reading into the suggestions. Thanks!

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report
Yes I agree with you but Dynamics CRM is a web application so the application can be access from any timezone . Lets say Mr. A has created one record from UK and Mr. B is accessing from Canada at the same time so Mr. B always see the record created 8 hours post from his local time. So he will confused and the created date will be show as future date time which is not acceptable. That is the reason CRM is saving with all date time converting to UTC format. So that in UI the date can convert to local date time. Here is the sample from UTC to Pacific or Canada.

var offset = -8; //For Canada and Pacific set offset var localdate = new Date(); //Local Time var utcConvertDate = localdate.toUTCString(); //Convert to UTC var currnetDate = new Date(utcConvertDate); //Convert to date object var datePacific = new Date( currnetDate.getTime() + offset * 3600 * 1000).toUTCString().replace( / GMT$/, "" )

Hope this helps you to understand.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I understand the timezone difference, but this does not explain why the offset is showing 7 or 8 hours into the future. The offset should be consistent across all records imported.

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

Hi,

As per your screenshot what I understand

informatica time 2:30 PM - A

Imported time 14:30 PM - B

CRM UI 9:30 PM - C

Now the problem is when CRM showing the data the UI it's treating database time 14:30 PM as UTC time and it's added 7 hours offset in the UI layer to make the local time .

Here is the resolution

Imported time should be 14:30 -7 = 7: 30 AM instead of 14:30 PM .

In summary you need to import all the datetime by converting UTC and always -7 hours .

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Goutam,

Thank you for your help thus far, but the time zone difference is not the issue. Please observe my 2 screenshots. Both records have the same Start time and Stop time, but they show two different resulting values on the right side of each screenshot. The only difference between the two records are the Date. These were both mapped using the same mapping with the same expression and -7 hours as you suggested. One record is correct and the other is one hour into the future from the correct time.

1st record (Incorrect - future time displayed) -

an entirely different record but same time as first. Same mapping used in Informatica - (Correct time displayed)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I found the answer - I found that the issue is caused by Dynamics accounting for daylight savings, which in turn means I must somehow account for it when inputting the dates. Daylight savings is roughly in March through November, so the UTC time offset will be either -7 or -8 to convert properly to my local time. The problem is that Daylight savings dates change each year. Is there a seamless solution to this?

Was this reply helpful? Yes No
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

Hi,

Can you please check the timezone behaviour of the 2 fields you mention above. This could be related to this if there is no change in informatica setting.

Refer this: https://technet.microsoft.com/en-us/library/dn946904.aspx

Hope this helps.

Was this reply helpful? Yes No
Alex Fun Wei Jie 33,628 on at

Like (0)

Report

Hi,

Refer below post for more options for a datetime control.

technet.microsoft.com/.../dn946904.aspx

Was this reply helpful? Yes No