2

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,

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32
Ruchita P
  • 359
  • 1
  • 4
  • 17

4 Answers4

4

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
Seymour
  • 3,104
  • 2
  • 22
  • 46
Giovanni Luisotto
  • 1,382
  • 7
  • 12
  • 1
    Changing the field's type is not compatible with direct query. One would have to switch to import mode. IMO if you're using direct query, there's a reason why. – rlb.usa Nov 25 '20 at 18:51
0

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

Raina Miranda
  • 11
  • 1
0

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

alex.stacked.718
  • 1
0

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.

Geoff Domal
  • 1