web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : ZmzDD9gUC+SqjsDRDDzzHX
Power Apps - Building Power Apps
Answered

Exception when Patch ' Type datetime2 is not a defined system type'

Like (0) ShareShare
ReportReport
Posted on 13 Dec 2019 15:25:01 by 91

I am trying to add a record to a SQL Servers 2005 table 

using:

Patch('[dbo].[SADailyCR2]', Defaults('[dbo].[SADailyCR2]'),{PD: 10 } , {MD: 110 }, {UD: 1110 }, {Date23: dtpD_1.SelectedDate })

and I get an error: The requested operation is invalid. Server Response: Microsoft SQL: Type datetime2 is not a defined system type."

 

The field Date23 is of a type [datetime],

SQL Server is v. 2005

 

Any help out there?

Thanks, Yakimo

 

How can I overcome that error?

If I avoid to add date in Date23, adding a new record if just fine

 
 
Categories:
  • Yakimo Profile Picture
    91 on 16 Dec 2019 at 15:33:49
    Re: Exception when Patch ' Type datetime2 is not a defined system type'

    Yes, my SQL server is 2005 and on-prem

    My SQL Server is 2005, so it seems that is the problem

    It seems that Patch get confused - too bad for me

     

     

  • Yakimo Profile Picture
    91 on 16 Dec 2019 at 15:32:16
    Re: Exception when Patch ' Type datetime2 is not a defined system type'

    My SQL Server is 2005, so it seems that is the problem

    As far as I know, DATATIME2 data type appeared in SQL Server 2008. 

     

    Since my server is older, it seems that Patch get confused - too bad for me

    Now for such a simple operation, I have to do work arounds to insert a date value 😕

  • Verified answer
    v-yutliu-msft Profile Picture
    on 16 Dec 2019 at 09:49:11
    Re: Exception when Patch ' Type datetime2 is not a defined system type'

    Hi @Yakimo ,

    Actually, using powerapps to update datetime2 in SQL Server works.

    I've made a similar test, my time column is datetime2.

    The formula that I use:

    Patch('[dbo].[food_info]',Defaults('[dbo].[food_info]'),{type:"aa",time:DatePicker1.SelectedDate})

    And it updates successfully.

    So I think that should be your SQL Server version problem.

    My SQL Server version: 2017

    What's more, if you want to filter based on datetime2 in powerapps, it is not supported.

    The following data types cannot be used as query option predicates:

    • date
    • datetime
    • datetime2
    • smalldatetime

    If you want to filter, you need to use this formula to transfer:

    YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])

    Anyway, updating is ok. So that should not be your formula or powerapps problem.

    Here's a doc about this connector for your reference:

    https://docs.microsoft.com/en-us/connectors/sql/

     

     

     

    Best regards,

  • Verified answer
    mcolbert Profile Picture
    126 on 13 Dec 2019 at 15:51:28
    Re: Exception when Patch ' Type datetime2 is not a defined system type'

    @Yakimo 

     

    SQL Server on-prem does behave a bit odd sometimes, especially with older versions of SQL.

    I would first check that the selecteddate has a value, if not you need to send blank() to update as a null.

    Also, check that you are using the latest gateway version (I am assuming this is an on-prem DB being that its 2005)

     

    One alternative you may try is calling a flow that executes a stored procedure to insert the row. This is the approach I typically use as it eliminates a lot of the issues you are seeing. If you try and use a datetime column in a query predicate it will either error or return 0 records on what appears to be a perfectly normal query.

     

    The other obvious benefit to calling a stored procedure is any additional work you may want to do in the sp that would otherwise be difficult or impossible in PowerApps (like updating multiple tables in a transaction). I also often use a single stored proc that will handle upserts (update/insert) as needed.

     

    There is more info here regarding the connector.

     

    Hope this helps.

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2