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.

View solution in original post

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.

View solution in original post

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.
CNT
Level: Powered On

Re: Now() & Azure SQL DB

Hi @RandyHayes 

Thanks for the tips. The following formula works

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

Highlighted
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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 411 members 5,651 guests
Please welcome our newest community members: