DST Time Issue - Crystal Reports using SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DST Time Issue - Crystal Reports using SQL

Author

Topic

wmahmud
Starting Member

2 Posts

Posted - 2010-05-03 : 13:14:27

We currently have custom reports setup in Crystal to capture data from our ServiceDesk. Everytime the time changes (March or November) we have our Resolve Date Time showing as 5hours+ of what it is in ServiceDesk. Eg. Ticket resolved at 2PM - but when run with Crystal it will show as 7PM.

Our Open Date Time seems to remain fine all the time.

Does anyone know what the issue may be or how we can go about resolving this? This is causing big issues for our reporting and throws times away off.

Here is our main crystal report SQL file with the date/time function. Maybe someone can quickly help?

Thank you.

------

USE [mdb]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--Install GetDateFromEpoch Function

if(exists(Select * from sysobjects where name = 'GetDateFromEpoch'))

BEGIN

drop function dbo.GetDateFromEpoch;

END

go

CREATE FUNCTION [dbo].[GetDateFromEpoch]

(

@Seconds int,

@ConvertToLocalTime bit

)

RETURNS datetime

AS

BEGIN

DECLARE @datLocalDateTime datetime

if(@ConvertToLocalTime = 0)

BEGIN

SET @datLocalDateTime = dateadd(s, @Seconds, '1/1/1970')

END

ELSE

BEGIN

DECLARE @datToCheck datetime

SET @datToCheck = dateadd(s, @Seconds, '1/1/1970')

DECLARE @intYear integer

DECLARE @strMar1 varchar(18)

DECLARE @strNov1 varchar(18)

DECLARE @DayOfTheWeek integer

DECLARE @DateDifference integer

DECLARE @datDSTStarts datetime

DECLARE @datDSTEnds datetime

DECLARE @intGMTOffset integer

/* Calculate when DST begins for the year in question */

SET @intYear = DATEPART(yyyy, @datToCheck);

SET @strMar1 = CONVERT(varchar(18), @intYear) + '0301 02:00:00';

SET @DayOfTheWeek = DATEPART(dw, @strMar1); /* Day March 1 falls on in that year */

if(@DayOfTheWeek = 1)

BEGIN

SET @DateDifference = 0 /* Sunday is 1st day of the month */

END

else

BEGIN

SET @DateDifference = 15 - @DayOfTheWeek; /* # of days between that day and the 2nd Sunday ("the second Sunday in March ", i.e. when DST begins)*/

END

SET @datDSTStarts = DATEADD(dd, @DateDifference, @strMar1);

/* Calculate when DST is over for the year in question */

SET @strNov1 = CONVERT(varchar(18), @intYear) + '1101 02:00:00';

SET @DayOfTheWeek = DATEPART(dw, @strNov1); /* Day Nov 1 falls on in that year */

if(@DayOfTheWeek = 1)

BEGIN

SET @DateDifference = 0 /* Sunday is 1st day of the month */

END

else

BEGIN

SET @DateDifference = 8 - @DayOfTheWeek; /* # of days between that day and the 2nd Sunday ("the second Sunday in March ", i.e. when DST begins)*/

END

SET @datDSTEnds = DATEADD(dd, @DateDifference, @strNov1);

/* Determine if the date in question is in DST or not */

IF @datToCheck BETWEEN @datDSTStarts AND @datDSTEnds

BEGIN

SET @intGMTOffset = -4 /* DST */

END

ELSE

BEGIN

SET @intGMTOffset = -5 /* No DST */

END

SET @datLocalDateTime = DATEADD(hh, @intGMTOffset, @datToCheck)

END

RETURN @datLocalDateTime

END

GO

wmahmud
Starting Member

2 Posts

Posted - 2010-05-03 : 13:53:12

This is the report where the issue is happening - others seem to be calculating things fine. Looks like the issue is only between March-November.

---

--Install Crystal_USD_SSP-Production-Outtage-Details

if(exists(Select * from sysobjects where name = 'Crystal_USD_SSP-Production-Outtage-Details'))

BEGIN

drop procedure [Crystal_USD_SSP-Production-Outtage-Details];

END

go

CREATE PROCEDURE [dbo].[Crystal_USD_SSP-Production-Outtage-Details]

@StartDate datetime,

@EndDate datetime

AS

BEGIN

SET NOCOUNT ON;

SELECT

call_req.ref_num as 'PersID',

dbo.GetDateFromEpoch(call_req.open_date, 1) as 'OpenDate',

dbo.GetDateFromEpoch(call_req.resolve_date, 0) as 'ResolveDate',

datediff(n,dbo.GetDateFromEpoch(call_req.open_date, 1),dbo.GetDateFromEpoch(call_req.resolve_date, 0)) as 'DurationMin',

ca_owned_resource.resource_name as 'ResourceName',

call_req.description as 'Description',

call_req.z_lessons_learned as 'Lessons_Learned'

FROM mdb.dbo.call_req call_req

INNER JOIN ca_owned_resource on call_req.affected_rc = ca_owned_resource.own_resource_uuid

WHERE (

(

((call_req.resolve_date is not null) and (dbo.GetDateFromEpoch(call_req.resolve_date, 0) between @StartDate and @EndDate)) or

(call_req.close_date is not null)

) and

(call_req.active_flag = 0) and

(dbo.GetDateFromEpoch(call_req.open_date, 1) between @StartDate and @EndDate) and

(call_req.z_prod_outage = 1) and

(

ca_owned_resource.resource_name in (

'Profile - IBS',

'Profile - FMS',

'Profile - Mortgages',

'DAWT - Toronto',

'DAWT - Ottawa',

'IVR - Toronto',

'IVR - Ottawa',

'Internet - Transactional',

'Filogix',

'Filenet',

'EWMS',

'Quick Modules',

'MAPS/CAPS',

'ABM',

'Internet - Static website',

'Orange Grove',

'Call Recording',

'Network - Toronto',

'Network - Ottawa',

'Email',

'Internet',

'BCP',

'CTI'

)

)

)

END

go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources