Wrong dates returned with sql query

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

SQL Server 2000 Forums

Transact-SQL (2000)

Wrong dates returned with sql query

Author

Topic

Sini
Starting Member

18 Posts

Posted - 2007-09-11 : 05:46:00

Hello people.

Would appreciate any help with the following query:

CREATE PROCEDURE dbo.spRpt_PatientsRegistered

(

@GroupDesc varchar(20),
@StartDate datetime,
@EndDate datetime
)

AS

/*
Adjust Start and End Dates

StartDate
1. Ensure date is without a time element by forcing it to a date without time
2. Add 16 hours to date, this forces the StartDate to be 4pm.
EndDate
1. Ensure date is without a time element by forcing it to a date without time
2. Add 40 hours to date. This forces the EndDate to be the following day at 4pm

Example (taken from specification)

if date range '21/03/2007' to '22/03/2007' is selected, the report
should pull all new registrations between 4pm on 21/03/2007 and
4pm on 23/03/2007.

*/

SET @StartDate = DateAdd(hh,16,CAST(Floor(CAST(@StartDate AS Float)) AS DateTime))
SET @EndDate = DateAdd(hh,40,CAST(Floor(CAST(@EndDate AS Float)) AS DateTime))

SELECT

Referral.DateFirstRegistered,
ISNULL(RTRIM(dbo.Client.Title), '') + ' ' + ISNULL(RTRIM(dbo.Client.FirstName), '') + ' ' + ISNULL(RTRIM(dbo.Client.LastName), '') AS FullName,
Client.FirstName,
Client.LastName,
Client.Address,
Client.Town,
Client.County,
Client.Postcode

FROM

Client

JOIN (
SELECT
ClientRef,
MIN(CommissionedDate) AS DateFirstRegistered -- This is actually the patients Referred Date
FROM
CarePackage
JOIN
CarePackageStatus ON CarePackage.CarePackageStatusRef = CarePackageStatus.CarePackageStatusId

WHERE

CarePackageStatus.CarePackageStatusName <> 'Cancelled'
GROUP BY
ClientRef
) Referral ON Referral.ClientRef = Client.ClientId

JOIN

[Group] ON Client.GroupRef = [Group].GroupId

WHERE

Referral.DateFirstRegistered >= @StartDate
AND Referral.DateFirstRegistered <=@EndDate
AND [Group].Description = ISNULL(@GroupDesc,[Group].Description)

Example of dates used:

Start date: 06/09/07
End date: 07/09/07

Sometimes this returns dates that are on the 08/09/07 which seems strange to me as it seems to have worked fine previously.

Can anyone please help?

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-11 : 05:54:02

Can you express dates in YYYYMMDD format and try?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 05:54:40

[code]SELECT Referral.DateFirstRegistered,
RTRIM(LTRIM(ISNULL(RTRIM(dbo.Client.Title), '') + ' ' + ISNULL(RTRIM(dbo.Client.FirstName), '') + ' ' + ISNULL(RTRIM(dbo.Client.LastName), ''))) AS FullName,
Client.FirstName,
Client.LastName,
Client.Address,
Client.Town,
Client.County,
Client.Postcode
FROM Client
INNER JOIN (
SELECT ClientRef,
MIN(CommissionedDate) AS DateFirstRegistered
FROM CarePackage
INNER JOIN CarePackageStatus ON CarePackageStatus.CarePackageStatusId = CarePackage.CarePackageStatusRef
WHERE CarePackageStatus.CarePackageStatusName <> 'Cancelled'
GROUP BY ClientRef
) AS Referral ON Referral.ClientRef = Client.ClientId
INNER JOIN [Group] ON [Group].GroupId = Client.GroupRef
WHERE Referral.DateFirstRegistered >= DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), '19000101 16:00:00')
AND Referral.DateFirstRegistered < DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), '19000102 16:00:00')
AND [Group].Description = ISNULL(@GroupDesc, [Group].Description)[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 05:56:55

"Start date: 06/09/07
End date: 07/09/07

Sometimes this returns dates that are on the 08/09/07"

That would be correct, wouldn't it, if they are BEFORE 4pm on the 8th September 2007?

My preferred way of getting "date without time" is to set it to Midnight using

DATEADD(Day, 0, DATEDIFF(Day, 0, @StartDate))

as this does not involve any datatype cating operations, which IME are slower.

Usually with this type of "Extend the EndDate to start of next period" the upper limit uses a LESS THAN test (rather than the Less Than OR EQUAL test that you have). I don't know if this is important to you, but thought I would just mention it

Kristen

Sini
Starting Member

18 Posts

Posted - 2007-09-11 : 05:59:34

Thanks for the responses.

Madhivana, i tried using your suggested date format but it returned the same results.

Could it be that the query is using FLOAT as casting intermediate from varchar to datetime be the main source of the problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 06:17:59

quote:
Originally posted by Sini

StartDate
1. Ensure date is without a time element by forcing it to a date without time
2. Add 16 hours to date, this forces the StartDate to be 4pm.
EndDate
1. Ensure date is without a time element by forcing it to a date without time
2. Add 40 hours to date. This forces the EndDate to be the following day at 4pm

The part in red tells you why you get records from "the day after"...

E 12°55'05.25"
N 56°04'39.16"

Sini
Starting Member

18 Posts

Posted - 2007-09-11 : 06:32:14

Yep peso you're right.

The problem is the interaction between the SSRS date parameters (which gives the ability to enter a from and to date) and the query which basically works only on a commission date (and adding 40 hours to it).

Thanks for the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 06:50:08

So my suggestion posted 09/11/2007 : 05:54:40 did not help?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 07:35:31

You get all the credit mate ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 08:09:56

Huh?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 10:34:59

"The part in red tells you why you get records from "the day after"...
...
Yep peso you're right."

I had already said "That would be correct, wouldn't it, if they are BEFORE 4pm on the 8th September 2007?"