[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date Parameter field in Crystal report 10 ....

Posted on 2006-03-23
7
Medium Priority
?
765 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
0
Comment
Question by:bhavesh55
7 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 16277557
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
 

Author Comment

by:bhavesh55
ID: 16277649
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 16277687
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 8

Expert Comment

by:dylanyee
ID: 16279223
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16279684
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
 

Author Comment

by:bhavesh55
ID: 16285933
dylanyee,
Thanks  a lot for writing exact syntax. I really appreciate that.

Again thanks to mlmcc.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16286318
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month20 days, 11 hours left to enroll

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question