THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

DATE function does not support all the dates in DAX by design #powerpivot #tabular #dax

The DATE function in DAX has this simple syntax:

DATE( <year>, <month>, <day> )

If you are like me, you never read the BOL notes that says in a clear way that it supports dates beginning with March 1, 1900. In fact, I was wrongly assuming that it would have supported any date that can be represented in a Date data type in Data Models, so all the dates beginning with January 1, 1900. The funny thing is that in some of the BOL documentation you will find that Date data type supports dates after March 1, 1900 (which seems not including that date, but this is a detail…). But we should not digress. The real issue is that if you try to call the DATE function passing values between January 1 and February 28, 1900, you will see a different day as a result.

evaluate row ( "x", DATE( 1900, 1, 1 ) )

-- return WRONG result
-- [x] 12/31/1899 12:00:00 AM

 

evaluate row ( "x", DATE( 1901, 2, 29 ) )

-- return WRONG result
-- [x] 2/28/1900 12:00:00 AM

 

evaluate row ( "x", DATE( 1900, 3, 1 ) )

-- return CORRECT result
-- [x] 3/1/1900 12:00:00 AM

As usual, this is not a bug. It is “by design”. The DATE function works in this way in Excel. And also in Excel it was “by design”. In this case the design is having the same bug of Lotus 1-2-3 that handled 1900 a leap year, even though it isn’t. The first release of Lotus 1-2-3 is dated 1983. I hope many of my readers are younger than that. I tried to open a bug in Connect. Please vote it. I would like if Microsoft changed this type of items from “by design” (as we can expect) to “by genetic disease”. Or by “historical respect”, in order to be more politically correct. Smile

Published Monday, November 05, 2012 2:52 PM by Marco Russo (SQLBI)
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ian Yates said:

November 6, 2012 5:51 AM
 

Laurent Couartou said:

Microsoft's answer is misleading.

DATE(1900,1,1) evaluates to 1 in Excel, but evaluates to 0 in PowerPivot.

In PowerPivot, 1900 is (correctly) not considered a leap year.

So, Microsoft actually fixed the leap-year issue by implementing this odd DATE function glitch.

Thanks for pointing that out.

November 15, 2012 10:12 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement