Power Pivot: Casting DateTime to Date in SQL Server source query

Standard

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 & 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 & 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 upfront in the source system query is a good thing to do. And I hope this is helpful.

Related Posts:
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!

 

Advertisements

Thank this author by sharing the article on social media. If you have any questions or comments, please leave a reply below:

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s