cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CNT
Super User
Super User

Now() & Azure SQL DB

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

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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.

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes 

Thanks for the tips. The following formula works

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

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 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (19,441)