Service Desk Practitioners Forum
cancel

Service Desk reports

Highlighted
Marnus Venter
Contributor.

Service Desk reports

We do Service Desk reports with MS Access. We use SD 4.5 SP11.

When we do reports through MS Access in daylight savings time the time on the report is 1 hour ahead. This only occurs during DST.

Hoping someone can help!

Thank you
4 REPLIES
Ivan Sakharov
Honored Contributor.

Re: Service Desk reports

You look directly in SD database (SQL or Oracle)? If so than the reason in SD way of time keeping - it is always +0 GMT without daylight saving correction.
If you want to see correct time in your reports you have to write a special function that add 1 hour to datetime if the date in daylight saving period and adds nothing if datetime not in this period. If you want I can share with your SQL function that makes such calculations.
Marnus Venter
Contributor.

Re: Service Desk reports

Thank you for your reply.

It would be great if you could forward me the SQL.

Thank you
Ivan Sakharov
Honored Contributor.

Re: Service Desk reports

This function is rather big but works fine.

It returns datetime in +3 or +4 GMT. I think you can customize it to your time zone.


Create function DaylightTime_GMT(@Custom_DT datetime) returns datetime
as
begin
Declare @DaylightTime_Start datetime, @DaylightTime_End datetime, @Result bit
select @DaylightTime_Start = case datepart(weekday,convert(datetime,'01.03.'+cast(YEAR(@Custom_DT) as varchar),104))
when 1 then convert(datetime,'29.03.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 2 then convert(datetime,'28.03.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 3 then convert(datetime,'27.03.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 4 then convert(datetime,'26.03.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 5 then convert(datetime,'25.03.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 6 then convert(datetime,'31.03.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 7 then convert(datetime,'30.03.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
end
select @DaylightTime_End = case datepart(weekday,convert(datetime,'01.10.'+cast(YEAR(@Custom_DT) as varchar),104))
when 1 then convert(datetime,'29.10.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 2 then convert(datetime,'28.10.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 3 then convert(datetime,'27.10.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 4 then convert(datetime,'26.10.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 5 then convert(datetime,'25.10.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 6 then convert(datetime,'31.10.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
when 7 then convert(datetime,'30.10.'+cast(YEAR(@Custom_DT) as varchar)+' 2:00:00',104)
end
If @Custom_DT >= @DaylightTime_Start and @Custom_DT < @DaylightTime_End Set @Result = 1 /* TRUE */
else Set @Result = 0 /* FALSE */

return Dateadd(hour,convert(int,@result)+3,@Custom_DT)
end


Marnus Venter
Contributor.

Re: Service Desk reports

No solution found