SQL Connector returns wrong dates

Bugs
1

I am calling a stored procedure in SQL using the SQL Connector and it appears to be returning the wrong value in the date field it returns. Seems to be one day off for all returned records.

Here’s the Design view:

design view.jpg635×688 65.2 KB

Here’s what it returns:

Yet, here’s the data returned when running the same stored procedure directly in SQL Studio:

Any ideas on what I might be doing wrong here?

Thanks.

2

The dates in the dropdown and the dates in SQL Studio are both hiding the time and the timezone offset, so its hard to tell what is happening.

I’d suggest checking …

  • The data type used in the column, the different types have different behaviours.
  • The data that was entered, did it have a time component? Was it entered as a string?
  • The timezone offset of the process entering the data, and the timezone of the database.
  • The timezone offset of the Bubble server. (Should be UTC).
  • The timezone offset of your browser.
  • The time component from the API.
  • The date from the API formatted to show in UTC.

For example, SQL Studio shows everything as midnight, is that for your timezone, for UTC, or is the time component dropped? If the Bubble server expects a time component, it might think that its for midnight UTC, and then show you the date formatted for your own timezone.

1 Like
3

@mishav, thanks. I was able to check the SQL Server and found it is using UTC (it’s hosted on Azure). The data in the SQL database where from a previous onsite SQL Server, so was not sure it’s original timezone. But since all seemed off by a day, I changed the dropdown formatting option to:

image.png1024×571 56.2 KB

Seemed to resolved the issue. Will need to further check how it writes new records and reports on them, but finally some progress - has been very frustrating. Thank you for helping to point in the right direction. Hopefully this can help others.

4

I expect you’ll have similar issues everywhere you use the date, good luck : )

5

Yes, unfortunately. I also found an interesting article (wely-lau.net/2011/07/10/managing-timezone-in-sql-azure-2) that states that those moving to MS SQL on Azure, with existing databases, will run into date/time issues due to Azure’s default timezone of UTC and what options you have. Thanks again.

6

This topic was automatically closed after 14 days. New replies are no longer allowed.