cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CNT
Level: Powered On

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
Super User
Super User

Re: Now() & Azure SQL DB

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
5 REPLIES 5
Super User
Super User

Re: Now() & Azure SQL DB

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
CNT
Level: Powered On

Re: Now() & Azure SQL DB

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.

Super User
Super User

Re: Now() & Azure SQL 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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
CNT
Level: Powered On

Re: Now() & Azure SQL DB

Hi @RandyHayes 

Thanks for the tips. The following formula works

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

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: Now() & Azure SQL DB

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

@TopShelf-MSFT 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 172 members 4,689 guests
Please welcome our newest community members: