cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate V
Advocate V

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

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

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

Highlighted
Advocate V
Advocate V

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.

Highlighted
Super User III
Super User III

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
Advocate V
Advocate V

Re: Now() & Azure SQL DB

Hi @RandyHayes 

Thanks for the tips. The following formula works

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

Highlighted
Power Apps
Power Apps

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

+ @Anonymous 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (8,480)