Power Platform Community Forum Thread Details

Hello,

I am setting up an MSSQL onprem server and want to have a date column in one table.

What I need to do with it:

1. PowerApps needs to write the current Date and Time into records stored in the SQL server

2. PowerApps needs to display the date (as dd.MM.yyyy) from those records

3. PowerApps needs to calculate the number of days between today and the date stored in the records

4. PowerAutomate needs to calculate the number of days between today the date stored and delete records that are older than 15 days and change a value in a record in a different table

In a test environment I have everything working with a VARCHAR in SQL that displays yyyy-mm-dd hh:mm:ss

Powerapps works nicely with it and in Power Automate I can create an Odata filter query that for all records that contain today's yyyy-mm-dd in that column and it'll delete the records from exactly 14 days ago. However, storing a date a string does not seem 'clean', but I'm no expert.

I'm in Germany, so UTC would be fine for this, but to be future proof, I'd like to understand how to display the proper date/time in my time zone.

If I understand things correctly PowerAutomate and Sharepoint work with 'points in time' that are recorded and then those points in time are translated into a time that is displayed given a specific time zone.

So, my question really is: what field type and format should I use when setting up the SQL server to play nice with PowerApps, PowerAutomate and Sharepoint, seeing that I want to be able to calculate dates, days and display the date/time in my timezone. (The database does not have contact with any other time zones)

Thank you guys.

Categories:

Building flows