CODEWITHSUNDEEP
powerbidaxpowerbi-desktopazure-sql-server
Ruchita P
Ruchita P

Reputation: 389

Power BI is converting date to datetime - DirectQuery (Advanced Option)

I have a PowerBI report connected to Azure SQL Direct Query Mode (Advanced Option - where we can write SQL Statements).

I got date column in SQL DB (date type), but PowerBI is converting this to datetime, even it’s in date type in SQL DB. Now I cannot transform this column in PowerBI as its Direct Query. However, if I connect directly to table (Direct Query mode only, but not advanced option) it takes date column as date.

Sample Date values:

created_date
2002-02-19
2002-02-19
2002-02-19
2002-02-19
2002-02-19

Converted Date values by PowerBI

created_date
19-02-2002 00:00:00
19-02-2002 00:00:00
19-02-2002 00:00:00
19-02-2002 00:00:00
19-02-2002 00:00:00

Any inputs on this will be highly appreciated.

Thank you,

Upvotes: 2

Views: 9583

Answers (4)

Geoff Domal
Geoff Domal

Reputation: 1

Question is old but I ran into this today. I solved this by creating a SQL view, cast datetime to date in the view then used DirectQuery without advanced option to grab the view.

Upvotes: 0

alex.stacked.718
alex.stacked.718

Reputation: 11

Use the DATEVALUE() function to convert a date to a datetime. This works with a direct query as well.

Upvotes: 1

Raina Miranda
Raina Miranda

Reputation: 11

you could just select the column and go to 'column tools' ribbon and change the format. to dd-mm-yyyy

Upvotes: 0

Giovanni Luisotto
Giovanni Luisotto

Reputation: 1400

That's a known problem, even if you use CAST or CONVERT to force the datatype in the query PowerBI will format it as a DateTime.

You can change it thought, from the report view:

  1. select the field (in the field bar)
  2. open the "Modeling" section in the top bar, here you will be able to change the data type and formatting of the field to Short Date

Upvotes: 4

Related Questions