Power Pivot: Casting DateTime to Date in SQL Server Source Query
DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table and it also creates problems while creating relationships with Date Dimensions. And so, it’s a common need to convert them to just Date before analyzing data and also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.
So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:
select [your-fields], cast([DateTime_Col] as date) as Date_Col from TableName |
Doing the data type conversion up front in the source system query is a good thing to do. And I hope this is helpful.
Related Posts: