Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
min-E
Helper I
Helper I

DirectQuery SQL Convert datetime to date

Using DirectQuery to pull a SQL query

The format of the date column is datetime '2023-01-01 00:00:00.000'

 

In SQL if I use the CONVERT below, I get the results wanted:

SQL:  convert (date, (date))

Results:  2023-01-01

 

However, when I put my SQL into PBI, the date column is displayed in its original datetime format. 

Are there any solutions or workarounds that don't involve Import Mode?

 

Thank you for your help!!

2 ACCEPTED SOLUTIONS

Another question, are you connecting to a table/view or are you using a stored procedure/sql command directly in Power BI? This can only be done if you are connecting to an actual structure and not using a sp or sql command within Power BI. 

View solution in original post

vineet_nair
Regular Visitor

The correct fix for this situation is to create a calculated column with DATEVALUE() formula in DAX. Spent hours trying to troubleshoot but this one finally worked. This is the only solution that worked for me in DirectQuery mode.

 

No other datetime to date conversions in PBI Desktop were effective since the sql query getting generated kept ignoring this data type conversion. However, using DATEVALUE(), it effectively creates the correct SQL query when fetching data and even works if you have joint this calculated column to columns from other DirectQuery tables.

See how it creates the SQL:
SELECT CAST(CAST(CAST([t3].[modifiedon] AS VARCHAR(4000))AS DATE) AS DATETIME) AS [_modifiedon1 (DateOnly)]
FROM table1 AS [t3]

View solution in original post

6 REPLIES 6
vineet_nair
Regular Visitor

The correct fix for this situation is to create a calculated column with DATEVALUE() formula in DAX. Spent hours trying to troubleshoot but this one finally worked. This is the only solution that worked for me in DirectQuery mode.

 

No other datetime to date conversions in PBI Desktop were effective since the sql query getting generated kept ignoring this data type conversion. However, using DATEVALUE(), it effectively creates the correct SQL query when fetching data and even works if you have joint this calculated column to columns from other DirectQuery tables.

See how it creates the SQL:
SELECT CAST(CAST(CAST([t3].[modifiedon] AS VARCHAR(4000))AS DATE) AS DATETIME) AS [_modifiedon1 (DateOnly)]
FROM table1 AS [t3]

min-E
Helper I
Helper I

I am using a sql query directly in pbi. Good to know that it isn't an option this way. Thanks for your reply.

The only option may be to use 'import mode' and a scheduled refresh.

Alex_Sawdo
Resolver I
Resolver I

Have you tried to change the data type from Date/Time to Date in Power Query? You can do this by selecting the column and changing the data type in the transform tab. 

Hello,

Indeed I have. It displays the message 'This step is not supported in DirectQuery mode. Switch to import.'

 

Another question, are you connecting to a table/view or are you using a stored procedure/sql command directly in Power BI? This can only be done if you are connecting to an actual structure and not using a sp or sql command within Power BI. 

I am using a sp. Tested on an existing table and the outcome was what I needed.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.