DataSource.Error: Microsoft SQL: 'datetime2fromparts' is not a recognized built-in function name.

[Vucko]

Hi,

I am getting error message 
DataSource.Error: Microsoft SQL: 'datetime2fromparts' is not a recognized built-in function name.

when adding a start of month in Power Query (Date.StartOfMonth())
Here's a code of the PQ step:

= Table.AddColumn(PreviousStep, "Start of Month", each Date.StartOfMonth([date]), type datetime)

I am having this issue only in Power BI desktop, in the service all seems to be fine.

Is this a new bug?

Thanks

[v-yetao1-msft]

Hi @Vucko 

I haven't found a similar known issue so far, what is your data source? What is the connection mode? Can you explain your steps in detail ? 

Best Regards,
Community Support Team _ Ailsa Tao

[Vucko]

Hi @v-yetao1-msft 

Data source is our SQL database.

Connection mode - getting the error both in import and directquery modes.
Error happens for Start of Month and End of Month functions in Power Query.

For Start of Month I get this error message: 
DataSource.Error: Microsoft SQL: 'datetime2fromparts' is not a recognized built-in function name.
For End of Month this error appears:
DataSource.Error: Microsoft SQL: 'eomonth' is not a recognized built-in function name.

Steps in detail:
Load any table from the SQL database which has a date/datetime column, transform this date column with one of the 2 mentioned functions (start of month / end of month).
Examples:
= Table.AddColumn(PreviousStep, "NewColumn", each Date.EndOfMonth( [date] ), type datetime)
or 
= Table.TransformColumns(PreviousStep, {{"date", Date.EndOfMonth, type datetime}})
If I break query folding before the start/end of month step, it works. (for example, adding index column before this step).
Error happens only for our SQL database, other sources like PBI dataflows work.

Probably a compatibility issue, but I'd still like to know what I could tell our IT so this compatibility can be resolved.

[v-yetao1-msft]

Hi @Vucko 

I tested with your query , everything is normal . What version of Desktop are you using to test ?

Best Regards,
Community Support Team _ Ailsa Tao

[Vucko]

@v-yetao1-msft I am using  2.106.582.0 64-bit

[v-yetao1-msft]

Hi @Vucko 

I tested it with Version: 2.105.1143.0 64-bit (May 2022) not Version: 2.106.582.0 64-bit (June 2022) .  Maybe you can downgrade Desktop to May version and retest .

Best Regards,
Community Support Team _ Ailsa Tao

[DaumantasJ]

I have the same errors and I believe that happens because SQL version I'm using is 2008R2, which microsoft does not support anymore, so any queries built against SQL by Pbi also use only functions supported by later SQL versions. And eomonth or datefromparts are supported from SQL 2012. Correct me if I'm wrong please.