We help IT Professionals succeed at work.

Date Parameter field in Crystal report 10 ....

bhavesh55
bhavesh55 asked
on
Medium Priority
781 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Author

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
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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
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.

Author

Commented:
dylanyee,
Thanks  a lot for writing exact syntax. I really appreciate that.

Again thanks to mlmcc.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.