KNOWLEDGE BASE

Error: "Error: 48031 mismatched case result types (case duration datetime)" When Replacing DATETIME Values Using MAKEDATETIME()


Published: 09 Mar 2017
Last Modified Date: 10 Mar 2017

Issue

When using the MAKEDATETIME() function to replace NULL date time values (DATETIME), the following error might occur:
Tableau Data Engine Error: 48031: mismatched case result types (case duration datetime)

Environment

Tableau Desktop

Resolution

Do not pass #12/30/1899# to MAKEDATETIME(). That day is the "base date" which will convert to 0 and return a NULL instead of the date value.

Instead, return #12/31/1899# and increment the other field by 1 day using DATEADD.

For example, to replace NULL time values with 10:00pm, change the formula from:
IF ISNULL([Field]) THEN
MAKEDATETIME(#1899-12-30#, #22:00:00#)
ELSE [Field] END
to
IF ISNULL([Field]) THEN
MAKEDATETIME(#1899-12-31#, #22:00:00#)
ELSE DATEADD('day',1,[Field]) END

Cause

In Tableau, #12/30/1899# is the "base date" added to time fields without a date. When a date is converted to an integer, it is stored as the number of days from that "base date". However, 0 does not return this date, it returns a NULL. Any other date will come back as the expected date.
Did this article resolve the issue?