Date do not display corrcetly

(0) Share

Report

Posted on by Community Member

Dear all,

On our custom quote report we have some fields like delivry date.
when we set on CRM online the delivery date with date time picker for the quote, when we print the report, we notice that the date it display with one day before .

The date field is define as : =Format(Fields!requestdeliverybyValue.Value,"dd/MM/yyyy")

Any idea, why in report my date is one day backward ?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

Royal King 27,686 on at

Like (0)

Report
Copy link

Link copied!

Mostly this happens because of time difference between users time zone.

CRM DateTime always comes with time component. There is a setting in creating a DateTime field that defines date only, but all that does is default the time to midnight local time

DateTime is saved in database as UTC time

DateTime in CRM UI is always shown based on user’s local time zone. This is true even if UI is only showing the date component. This leads to following effect:

If user in CST enters contact’s birthdate as 2/2/1980, this is saved as 2/2/1980 05:00 in DB

If user in PST views the contact record, he/she will see the birthdate as 2/1/1980 (because local time for the user will be 2/1/1980 22:00)

DateTime retrieved through CRM Web Services is always UTC time

DateTime set through CRM Web Services is user’s local time zone by default

Note that extra care must be taken to understand whether the call is done with the calling user or a service user

DateTime set through CRM Web Services can be defined to be UTC instead

DateTime queried directly from SQL table or base view returns UTC

DateTime queried from filtered view returns users local time

Check this post that provides how to avoid this issue using javascript

http://www.powerobjects.com/blog/2012/09/11/crm-2011-date-time-incorrect-dates-time-zones/

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

In order to build my report I am using FetchXML where I take directly the dateTime field from Quote entity.

So how can I make it work in order to reflect on report what I have on CRM online Quote view ?

regards

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report
Copy link

Link copied!

if same person views the records in the CRM view and report , he wont see any difference. CRM always converts datetime into local users time zone. Are you sure same person seeing different dates in CRM UI and report?

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (1)

Report
Copy link

Link copied!

Hi Sergec,

Actually, it is usually depends on your timezone agains the UTC format.

UTC is the one which stored in CRM Database.

Check your personal option, timezone:

The report, executed by user, for any fields about date and time, if you use fetch xml or filtered view (SQL onpremise) will return based on the user personal option timezone

Thank you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hello Chitrarasan,

let me describe.

IN my CMR on line I defined the following field for quote :

EffectiveTo which is set as 8/16/2014 ( English format)

Then when I display that effectiveTo field on my report with expression :

=Format(Fields!effectivetoValue.Value,"dd/MM/yyyy")

My report diplay the field as : 15/08/2014 which is correct format except that the DAY part is -1 from what is set in CRM.

This is what I try to figure out where it comes from

reagrds

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

@Aileen, The time zone is set correctly as GMT +1 in CRM, I am based in France and my PC as same time zone

Any idea?

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report
Copy link

Link copied!

can you see correct date if you remove the date format ("dd/MM/yyyy") in the report?

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Ok I manage to solve it by going to my personal setting and selecting Format tab.

My Time zone is set to GMT+1, then have to set my Format to French

Then from my field display on my report I remove the format method and keep the field alone.

Then all goes ok even if its weird for me to explain

SOunds logic to you ?

Was this reply helpful? Yes No