Records missing for end date in report

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

Records missing for end date in report

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-11-03 : 00:14:18

hello friends..
i aleays facing this problem while reporting....

if i want to show report for date range then i am not getting records for end date...why???

my report query was

select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0),
isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount
from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID
inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate
between '10/01/2006' and '10/31/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate
order by 3

i am getting rows 15 here but when i fired this

select distinct DwnDate,isnull(D.FileName,'No File Found'),isnull(H.File_ID,0),
isnull(C.DownLoadCatname,'No Category Found'),count(H.File_ID) AS TotalCount
from DownLoadHistory H inner join DownLoad D on H.File_ID = D.File_ID
inner join DownLoadCat C on D.File_Cat = C.DownLoad_CatID where File_DwnDate
between '10/01/2006' and '11/01/2006' group by D.File_Name,C.DownLoad_Catname,H.File_ID,File_DwnDate
order by 3

then i am getting rows 16

previous one i always missed records on 10/31/2006...is there any solution or i always add one day to end date and then get values??

please help me out

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-03 : 01:00:05

that's is because File_DwnDate contains date & time
"between '10/01/2006' and '11/01/2006'" basically means >= '2006-10-01 00:00:00' and <= '2006/10/31 00:00:00'
that's why the record with date 2006-10-31 hh:mm:ss (after midnight) is not in.

change to


where File_DwnDate >= '20061001'
and   File_DwnDate <  '20061101'

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-03 : 01:03:53

It seems that File_DwnDate contains a time part (once in a while), so yes you need to add one day to make sure to catch them all.
You could trim the time part of the date, but that will prevent the query to use an index on the date.

PS. Consider writing string dates as 'YYYYMMDD', it is portable between different language settings (and readers of this site

)

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-11-03 : 01:31:42

HEy ppls...

Thanks for tht valuable tip........

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-03 : 06:49:21

http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources