Search code examples
sql-serverssasdaxssas-tabular

SQL Server dates are converted to date/time in Analysis Services

I have a database in SQL Server that also has a related tabular database in Analysis Server. One of the tables has columns of type date in SQL Server:

enter image description here

If I run a SQL select in SQL Server select * from app_dates , I get the dates:

enter image description here

But if I run the equivalent in DAX evaluate app_dates I get date/time values instead of dates:

enter image description here

The problem is that my program detects automatically the types, and the type that Analysis Services returns is date/time instead of date.

How to tell Analysis Services that the column type should be date ?

Solution

  • Change the data type and data format of that column in your SSAS model. You can do this by viewing the properties of the column. I have an example below how to get the MM/DD/yyyy format you want without the time.

    https://learn.microsoft.com/en-us/analysis-services/tabular-models/column-properties-ssas-tabular?view=asallproducts-allversions

    MMddyyyy example