Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / CRM 365 On Prem - Foun...
Microsoft Dynamics CRM (Archived)

CRM 365 On Prem - Found a bug in the CRM SQL scalar function dbo.fn_UTCToTzCodeSpecificLocalTime which breaks Filtered Views in some cases

(0) ShareShare
ReportReport
Posted on by samalex 355

Hello,

I've been trying to track down an issue with using Filtered Views and Reports, and I believe the issue is a bug in the SQL scalar function dbo.fn_UTCToTzCodeSpecificLocalTime.  The issue is we have dates in our environment that are 9999-12-31, and this function his this line:

select @FuzzySearchDateTime = dateadd(day, 1, @UTCTime);

Well adding 1 day to 9999-12-31 is not possible, so when I call the Filtered View on this entity table I get this error:

Msg 517, Level 16, State 1, Line 1

Adding a value to a 'datetime' column caused an overflow.

I see this as a bug because it's checking for the minimum possible date in the function but not the highest possible date before this line of code.  So my options are editing the function which is easy but not a supported fix or changing all my data and processes to not use 9999-12-31 but use something earlier (9998-12-31 for example).  At any rate I wasn't sure who to pass this bug to or if someone from Microsoft could suggest a supported way to edit this View so it wouldn't blow-up on me.

Thanks --

Sam

*This post is locked for comments

  • samalex Profile Picture
    samalex 355 on at
    RE: CRM 365 On Prem - Found a bug in the CRM SQL scalar function dbo.fn_UTCToTzCodeSpecificLocalTime which breaks Filtered Views in some cases

    Quick update, my correction to this in our Test environment was to change this:

    if @UTCTime <= '1/2/1753'

    begin;

    return @UTCTime;

    end;

    select @FuzzySearchDateTime = dateadd(day, 1, @UTCTime);

    to this:

    if @UTCTime <= '1/2/1753' or @UTCTime >= '1/31/9999'

    begin;

    return @UTCTime;

    end;

    select @FuzzySearchDateTime = dateadd(day, 1, @UTCTime);

    which will return the date from the function if it's the least possible date or the max possible date.  I just wanted to get confirmation from someone at Microsoft before looking at this as a permanent fix since this is changing one of the stock CRM SQL Functions.

    Thank you.

    Sam

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans