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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.