Date Parameter field in Crystal report 10 ....

Dear Experts,
I am using stored procedure with my crystal report.
->My Stored Procedure is like:

Create Procedure.....
@FromDate datetime,
@ToDate datetime
AS
Select
...
...
From
...
..
Where
Table1.Date >= @FromDate
Table1.Date <= @ToDate + ' 11:59 PM'

->When i run this stored procedure in SQL ANALYZER it returns 2 records for the @FromDate = '3/22/2006' & @ToDate = '3/22/2006'.which is CORRECT.

->When i click on parameter button in report:
I enter following values for the parameter:
@FromDate
@ToDate
Discrete Value:
For @FromDate: 3/22/2006 12:00:00 AM
    @ToDate: 3/22/2006 11:59:59 PM


Report shows me 3 records.In that there is one record of date '3/23/2006 3:20:24 PM' which shouldn't be there.

->Note:
Date field's values appears in sql analyzer as follows when i run my stored procedure in SQL Analyzer:
2006-03-22 15:20:24.000
2006-03-22 15:39:15.123
2006-03-23 15:20:24.000

In table it looks like:
3/22/2006 3:20:24 PM
3/22/2006 3:39:15 PM
3/23/2006 3:20:24 PM

How do i fix this problem ? Please let me know as soon as possible.

Thanks,
bob
bhavesh55Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Why are you adding the 11:59 PM?

THe date you pass in from the report is

@ToDate: 3/22/2006 11:59:59 PM

When you ADD 11:59PM - essentially 24 hours your todate for comparison is  3/23/2006 11:29:58

Delete the addition and pass in the values you want to use.

mlmcc
bhavesh55Author Commented:
Dear mlmcc,
From VB application, i am passing date parameter FromDate = '3/22/2006' & ToDate = '3/22/2006'.In table,it is "Datetime" datatype and also in stored procedure i can only able to define "@FromDate datetime" which i defined.


If i write report's stored procedure like this:
Create Procedure.....
@FromDate datetime,
@ToDate datetime
AS
Select
...
...
From
...
..
..
Where
Table1.Date >= @FromDate
Table1.Date <= @ToDate


When we run the report from vb application,report is getting @FromDate = '3/22/2006' & @ToDate = '3/22/2006' and  report is NOT showing any records.

So what should i do in order to bring those records ?

Thanks,
bob
mlmccCommented:
If you are passing just a date then you need to add the time.  In your question above you indicated you were passing the time with the date.

From the question
->When i click on parameter button in report:
I enter following values for the parameter:
@FromDate
@ToDate
Discrete Value:
For @FromDate: 3/22/2006 12:00:00 AM
    @ToDate: 3/22/2006 11:59:59 PM


If you want to pass just the date the other thing you could do is pass todate + 1 (in this case 3/23/2006)

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

dylanyeeCommented:
what mlmcc suggested is right, when doing filtering for your records, try filter the to date + 1 day, eg)

Where
Table1.Date >= @FromDate
AND Table1.Date < dateadd(day, 1, @ToDate)

Then you could pass in parameter @FromDate = '3/22/2006' and @ToDate = '3/22/2006'

dylan
wykabryanCommented:
A side from this suggestion, which I agree with, if time is important make sure you are using military time.  I have notice some weird things happen with trying to use standard time.  Meaning, in your time of 11:59:59 PM -> 23:59:59. this will ensure that you pick up the rest of the records.  This is the same as just adding 1 date to the end date.
bhavesh55Author Commented:
dylanyee,
Thanks  a lot for writing exact syntax. I really appreciate that.

Again thanks to mlmcc.
mlmccCommented:
Glad i could help

FYI - You can split points.  If dylan's comment also helped then I can reopen this and allow you to split the points.

mlmcc
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.