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?
 
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
0
 
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
0
 
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
0
 
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.
0
 
bhavesh55Author Commented:
dylanyee,
Thanks  a lot for writing exact syntax. I really appreciate that.

Again thanks to mlmcc.
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.