Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

We've captured the moments from FabCon & SQLCon that everyone is talking about, and we are bringing them to the community, live and on-demand. Starts on April 14th. Register now

Vucko

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

Submitted by Advocate II on ‎06-19-2022 11:25 PM

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

Status: Investigating
See more ideas labeled with:
6 Comments (6 New)
Comments
Anonymous
Not applicable
‎06-20-2022 10:40 PM

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
Advocate II
Advocate II
‎06-21-2022 07:23 AM

Hi @Anonymous 

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.

Anonymous
Not applicable
‎06-22-2022 12:26 AM

Hi @Vucko 

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

Ailsamsft_0-1655882727286.png

Best Regards,
Community Support Team _ Ailsa Tao

Vucko
Advocate II
Advocate II
‎06-24-2022 08:00 AM

@Anonymous I am using  2.106.582.0 64-bit

Anonymous
Not applicable
‎06-27-2022 12:56 AM

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
New Member
‎07-25-2022 03:40 AM

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.

Idea Statuses