Knowledge Base

Snippets

23

// AZURE SQL USES GREENWICH MEAN TIME . . . Azure SQL Database does not support time zone settings; it always follows UTC. Use AT TIME ZONE in SQL Database if you need to interpret date and time information in a non-UTC time zone.

 

IF  ServerProperty('EngineEdition') >= 5

Begin

set @current_utc_offset = (select  convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time')

End

 

Then I use this information to adjust the query data

 

SELECT  dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0)) as TimeStampHour

 

Complete Query Used for report:

 

IF  ServerProperty('EngineEdition') >= 5

Begin

set @current_utc_offset = (select  convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time')

End

 

 

if(@ServiceLocation='0' or @ServiceLocation is null)

begin

       set @ServiceLocation=null;

end

 

SELECT  dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0)) as TimeStampHour

, Count(*) AS ClientsServed

FROM  [GIBS_FBClientsVisits]

where

  ( [CreatedOnDate]

    BETWEEN @StartDate

     and @EndDate)

    and  (@ServiceLocation is null or GIBS_FBClientsVisits.ServiceLocation=@ServiceLocation)

GROUP BY dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0)

ORDER BY dateadd(hour, datediff(hour, 0, [CreatedOnDate]), 0)

 

Comments

There are currently no comments, be the first to post one!

Post Comment

Only registered users may post comments.