DAX (Direct Query) and Power Query (Import) sql date datatype cast to datetime and datetime2

[jpretorius]

The SQL generated by a DAX expression against a DirectQuery datasource, by default, casts dates and datetimes as a SQL datetime datatype e.g. cast('20190909' as datetime).

The SQL generated by a M expression, by default, cast dates and datetimes as a SQL datetime2 data type with default precision e.g. cast('20190909' as datetime2).

Is there any way to change this default behaviour i.e. native query for a date column uses cast as date instead of datetime/datetime2

For example:

Power Query (M) -

The following Power Query statement is used to filter a monthly slice of the data in Power BI when using the import storage mode.

let
    Source = Sql.Databases("mydbserver.database.windows.net"),
    mydb= Source{[Name="mydb"]}[Data],
    dbo_MonthlyStuff = mydb{[Schema="dbo",Item="MonthlyStuff"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_MonthlyStuff, each ([MonthDateKey] = #date(2019, 2, 1)))
in
    #"Filtered Rows"

Query folding takes place, producing the following native query (below). The correct data type (date) has been detected by Power BI however the native SQL query converts the filter to a datetime2:

select [_].[MonthDateKey],
    [_].[TotalAmount]
from [dbo].[MonthlyStuff] as [_]
where [_].[MonthDateKey] = convert(datetime2, '2019-02-01 00:00:00')

DAX

If I use DirectQuery storage mode in Power BI and run a calculation against a table containing a SQL Date data type, filtered to a particular month, I see a similar issue.

In Power BI I have a slicer on the MonthDateKey column (set as a date datatype) and a measure, that sums TotalAmount, displayed in a card visual.

Total Amount =
SUM ( MonthlyStuff[TotalAmount] )

Using DAX Studio or Azure Data Studio to trace the above, I see the following SQL is generated by the DAX expression:

SELECT SUM([t0].[TotalAmount])
 AS [a0]
FROM 
(
(select [$Table].[MonthDateKey] as [MonthDateKey],
    [$Table].[TotalAmount] as [TotalAmount]
from [dbo].[MonthlyStuff] as [$Table])
)
 AS [t0]
WHERE 
(
[t0].[MonthDateKey] = CAST( '20190201 00:00:00' AS datetime)
)

The data type for MonthDateKey in Power BI is configured as a date but in the SQL generated for DirectQuery it is cast to a datetime. Is there any way to change this default behaviour such that the SQL will cast to date instead of datetime?

Any help would be much appreciated

[v-qiuyu-msft]

Hi @jpretorius, 

Thank you for your feedback. I would suggest you post an idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas

Best Regards,
Qiuyun Yu

[jpretorius]

Hi Qiuyun Yu,

Thank you for getting back to me.

Apologies, let be more clear on the request. At this stage, all I'm looking for is confirmation on the current behaviour and confirmation that the current behaviour cannot be changed.

To clarify: 

1. By default, dates & datetimes in DAX measures will be converted to a SQL datetime data type when using DirectQuery?
2. By default dates & datetimes in M/PowerQuery will be converted to SQL datetime2 when query folding takes place?
3. At present, the above behaviour cannot be modified?

If you could confirm the above it would be very much appreciated.

Many Thanks

James

[jpretorius]

Hi @v-qiuyu-msft 

Are you able to provide an update on the above 3 statements?

Have a great weekend. 

Many Thanks

James