Now() & Azure SQL DB

[CNT]

Hello,

I am using Now() to get the current time and date to be stored in an Azure SQL DB. The Field in the table is of type datetime.

When using the Now(), Powerapps returns the correct date & time. But, when I patch it into the Azure SQL table, the date is OK but the time is 2 hours behind (I think that's because the DB is hosted in a different TimeZone).

I don't want that to happen. I want my local time (as in my PC) to get patched to the Azure SQL Table.

Any help would be much appreciated.

Thanks

[RandyHayes · Accepted solution]

@CNT 

You will need to look at adjusting the TimeZoneOffset of your dates you get from SQL.

Consider using this on your dates from SQL:

   DateAdd( yourSQLDataField, -TimeZoneOffset( yourSQLDataField), Minutes )

That will adjust the date you get to the proper value.

I hope this is helpful for you.

[CNT]

Hi @RandyHayes 

Thanks for the quick response. In my case, the problem is not reading from Azure but writing to Azure. For example, when my local time is 15:21, it gets written as 13:21 (2 hours behind) in the DB.

[RandyHayes]

@CNT 

Yes, it is converting it to UTC.  There is no way to change that (that I am aware of).  So, you will always have UTC times in the DB and will need to adjust from UTC (with that formula) to get the correct values. 

This article was the closest I could find based on that particular issue of setting your timezone for the DB.

[CNT]

Hi @RandyHayes 

Thanks for the tips. The following formula works

DateAdd(Now(), -TimeZoneOffset(Now()),Minutes)

[Mr-Dang-MSFT]

As @RandyHayes mentioned, DateTimeOffset works best with SQL + PowerApps.

Here's a blog post on this exact scenario:

https://powerapps.microsoft.com/blog/working-with-datetime-values-in-sql/

+ @CarlosFigueira who had written for any additional clarification you may need

+ @Anonymous