Soooo I'm not sure if this is the right place to ask or not and my apologies if it is not. Whether this should be asked in one of the Azure Forums.
We are kind of brainstorming the best way to attack a DATETIME Parameterized Query against Microsoft Azure SQL Server. Since we need to concern ourselves with Universal Time Coordinate(UTC) and GMT, we do believe, should a DATETIME Data Column be converted to a Date first? Or do we need to take into account the -5 hours of UTC/GMT and go after data that way.
We are trying to determine the best way to parameterize our DateTime/Date queries when our data column is a DATETIME Data Type and will be expressed in UTC when it's on a Microsoft Azure SQL Server(Is that correctly phrased? "Microsoft Azure SQL Server"?)
Any insight that anyone could provide would be GREATLY appreciated which takes into account UTC and trying to query based on that.
Thanks in advance for your review and am hopeful for a reply.
Well, it all depends on things you did not tell us. What we do know is that sysdatetime() and getdate() return the current time in UTC. But that does not mean that all your datetime values will be in UTC. I mean, if you are getting date and time from some external source in your neighbourhood, will you shift them to UTC before you store them?
Then again, it certainly helps if you have the same time zone across the database and query it consistently.
If you are dependent on sysdatetime/getdate and use these for default, you could encapsulate them into a UDF that slashes off five or four hours, depending on you are on DST or not. Unfortunately, such a UDF will introduce performance issues, as it will not be inlined.
So maybe it is better to store all datetime values in UTC, and have the client layer to do the translation. That could also have a benefit, if you suddenly have users in other time zones who enter data in their time.
Hi
@Bobby P
,
Suggest you starting from Below MS document, it will show you more details information. It is also supported in Azure SQL server.
Date and Time Data Types and Functions (Transact-SQL)
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
No offense...but I'm really looking for used solutions...how one's shop utilizes Azure UTC and date parameter queries using a DATETIME Data Column.
For example, if I wanted to run for last week...01/24/2022 Thru 01/28/2022 and the data column we want to filter on is in UTC DATETIME format.
I do believe that we have tried to use AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' using >= and < but that didn't seem to return all possible rows in our testing.
So we ended up doing a Conversion to DATETIME and then using those Derived Values to filter our result set WHERE [TableName].[CreatedOn] is a DATETIME Data Type expressed in UTC...
DECLARE @StartDateDeriveUTC DATETIME
= COALESCE(
DATEADD(
MINUTE,
ABS(DATEPART(TZ, CAST(@StartDate_In AS DATETIME)AT TIME ZONE 'Eastern Standard Time')),
CAST(@StartDate_In AS DATETIME)AT TIME ZONE 'UTC'),
'19000101'),
@EndDateDeriveUTC DATETIME = COALESCE(
DATEADD(
MINUTE,
ABS(DATEPART(
CAST(@EndDate_In AS DATETIME)AT TIME ZONE 'Eastern Standard Time')),
CAST(@EndDate_In AS DATETIME)AT TIME ZONE 'UTC'),
GETUTCDATE());
