Patching SQL Table with datetime column

[lmheimendinger]

I am building an app that needs to create a new record in a SQL table (that I cannot control the column settings).  A datetime column in SQL has data that looks like this:

2021-12-04 09:54:28.000

Try as I might, I am unable to get a datetime value in Power Apps to format like this.  I can get a text field by using

Text(datetimevalue("2021-12-04 00:54:28"), "yyyy-mm-dd hh:mm:ss")

However, I cannot use a text value to patch a datetime SQL column, and I cannot figure out how to convert a text value as in the above example into a similar datetime value.

As I said in my opening, this is a production database that I cannot control formatting, so I can't change the column to text or anything else.

Surely we have not been left hanging with this issue about SQL, so if you can help it is much appreciated.

[lmheimendinger · Accepted solution]

I believe I have come up with a solution.

i created a new table that has two columns, ID and DATETODAY.  The latter has a default value of GETDATE() and allows NULL.

When I create a new record with ID of 1 and leave DATETODAY blank, it creates a record with the current date and time.  I will collect this record, use the column as a value when I PATCH the original table for all the datetime columns it requires, then delete the record.  

I should have it tested by tomorrow.

[lmheimendinger]

I appear to be mistaken.  When Power Apps gets the SQL data into a collection, the field appears to be a TEXT value

Here is the record in the SQL table

And here it is, collected and displayed in a gallery

If I add a label in the gallery (in green) and use the column name in TEXT properties the label ends up as a text value. 

So, is there a way to get the data from the SQL column as "raw" data and not text data?  Is there a way to reference the collection column and ignore the gallery value, or another way to insert something into a gallery besides a label, or a way to modify what the data might contain?

Driving me crazy. 

[lmheimendinger]

This doesn't work because the column comes into Power Apps as a date text string and SQL barfs on it.

[lmheimendinger]

Power Automate seems to get the SQL column correctly, so the solution may be to call Power Automate to create the record..  Stay tuned.  A lot of info to pass back and forth.  Ugh.

[lmheimendinger · Accepted solution]

No issue at all with Power Automate.  I was able to create a new item/row from several forms of datetime when the SQL column has a datetime definition.  Why this isn't supported in Power Apps is baffeling.

[RatanKP]

Hai @lmheimendinger ,

am also facing the similar kind of issue, request to updated the solution you created to resolve the issue,which will helps me to fix my issue as well.

Thanks in advance