Error issue on Date fields

[Holly_CMS]

Hi - I am getting this error from the flow. I am not sure if the date fields from SQL into Sharepoint list date columns data values are the issue??

Flow save failed with code 'OpenApiOperationParameterValidationFailed' and message 'Input parameter 'item' validation failed in workflow operation 'Create_item': The parameter with value '"@items('Apply_to_each')?['CreatedDate']"' in path 'item/CreatedDate' with type/format 'String/date-no-tz' is not convertible to type/format 'String/date-time'.'.

I have one date field that both SQL and SP agree but the other three date fields, nope...

[abm]

Hi @Holly_CMS 

Please have a look at this thread answered by @DamoBird365 

Solved: String/date-time error ( apps to automate) - Power Platform Community (microsoft.com)

Thanks

[wskinnermctc]

Can you show the column types from your SQL table and the column types from your SharePoint list? And also say if you have Date Time column types from your SharePoint list are set to Date Only or set to Date and Time.

Identify which column is working "both SQL and SP agree" and identify which columns are not.

If you don't want to show your table info with screenshots, just list the names and types.

SQL Server Table - Column TypesSharePoint List Settings - Column Types

Is your flow simple as in Get Rows from SQL and the Create Item in SharePoint? Or is there other parts of your flow that are changing the data?

[Holly_CMS]

Is your flow simple as in Get Rows from SQL and the Create Item in SharePoint? Yes

Or is there other parts of your flow that are changing the data? No 

Gimme a bit to get these pictures for you.

[Holly_CMS]

The only date field that did not fail in the flow is the OpenedDate.

The rest did. 

I see that the OpenedDate data type in SQL is different than the others.  Originally in SP, I had it Text data type but I didn't like the outcome of it so I changed these date columns into Date/Time data type and then the error popped up for the CreatedDate, ModifiedDate, and DeletedDate.

[wskinnermctc]

Yeah those column types in the SQL table "smalldatetime" are going to be a problem.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ve... 

You need to change those "smalldatetime" sql columns into a "date" type.

[Holly_CMS]

Got it - I have informed our SQL database developer to create another table specifically for the Power Automate to change from smalldatetime to date and then I will test again 🙂

Will keep you posted. 

Thank you!

[wskinnermctc · Accepted solution]

I don't know what all you plan on doing, but if you look at my SQL example you can see that I have 3 columns that refer to date.

ORIG_STATE_HIRE_DATE - nvarchar(8) - will be 20230723 as in text yyyyMMdd

Date_ORIG_STATE_HIRE - date - will be  2023-07-23 as in a date yyyy-MM-dd

DateTZ_ORIG_STATE_HIRE - datetimeoffset - will be 2023-07-23 00:00:00.00 -05:00 as in a datetime with the timezone

I added the datetimeoffset column to my tables to use with the tables in the Microsoft Dataverse and Dataflows. 

If you use the datetimeoffset type column with Dataflows, you don't have to deal with the conversions around UTC problems.

So something to keep in mind if you plan on expanding how much you use SQL Server. I started with getting things from SQL and then putting them to SharePoint Lists, but now I found it easier to go from SQL to DataverseTable to SharePoint lists.

Using Dataverse as a source is usually easier when dealing with power automate. So if you have something that you think will grow in functionality, then go ahead and put the datetimeoffset columns in the SQL server.

It can help with some time conversions when going into Power Automate or Dataverse. 

You can't go directly from SQL server datetimeoffset type column to a SharePoint List datetime column without a conversion. However, you can easily use the converttimezone() on a datetimeoffset column. 

[Holly_CMS]

Yeah, unfortunately, we aren't using dataverse. However, this could be a good discussion for my boss and the CTO to why we would need to use dataverse - as a middle person....They didn't like how limited dataverse is - it's like the Quickbase where it's good for anything internal but expensive to use API to get the external data in.....We have a lot of third party apps that we are working to offload and build our own power apps for some of them. 

I am doing a project...a big one...where we are diverting the data from Wrike to Power Apps but one of the processes/dataset is in SQL. That I am working on but I am just trying to get familiar with the Power Automate/SQL connectivity for the meantime...and tackle stuff like this before I am to start building an official flow if that makes sense to you?  

[Holly_CMS]

I will check out the conversion as pointed out in the video that Abm linked in this post as well.

[wskinnermctc]

Well I can't use the 'real' dataverse due to how my organization is set up. I use Dataverse for Teams which is like a smaller dataverse with a little less functionality. It is free and I'm not really using it to run organizational apps, so it works for me.

[Holly_CMS]

Ahh....Honestly, I am not sure what a Teams Dataverse is like?