?
Solved

How to use a date parameter in a SQL Query from Crystal Reports reading a Sybase Database

Posted on 2011-02-22
8
Medium Priority
?
933 Views
Last Modified: 2012-05-11
Hi Experts,

I'm trying to build a SQL Query from Crystal Reports with parameters. The database is Sybase. Each time i try something it gives me an empty report. My field is a datetime and I want to be able to have a particular date, like this :

iAgentPerformanceStat.Timestamp = {?Date}

That's what I tried last :
convert(char(10),iAgentPerformanceStat.Timestamp,101) = convert(char(10),{?Date},101)
//where {?Date} is a date not datetime format. Maybe that's the problem...

Can you help me?

P.S. I dont want to use the regular parameter from Crystal cause it takes too long the run the report like this (40 minutes), that's why I'm using the SQL query to help me.
0
Comment
Question by:Etdashou
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 34956593
Are you building the query is the database or trying to use a Crystal Command?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 34959630
The fact that the parameter is a date instead of datetime theoretically shouldn't matter for the test that you posted, since it's converting both the field and parameter to mm/dd/yyyy strings (assuming that the 101 format is the same as in MS SQL).  I'm not familiar with Sybase, but what you posted looks like it should work.  About all that I can think of is to try to make sure that the field and parameter values are what you think they are, and are being interpreted correctly.  You could try identifying a specific datetime value that's in Timestamp, changing your parameter to datetime, using a simple test without the CONVERT's, and seeing if you can use the datetime parameter to find that specific value.  If so, then the basic test (datetime to datetime) is working.  If not, then maybe the Timestamp field isn't what you think it is.

 James
0
 
LVL 1

Author Comment

by:Etdashou
ID: 34961305
Hi mlmcc, I'm using the Command of Crystal to put my SQL Query.

For James, I tried a lot of different things and it seems that a simple compare give me an error as well :

iAgentPerformanceStat.Timestamp = {?Date}

and it gives me a blank report.

I tried my formula to have the last day :

(iAgentPerformanceStat.Timestamp>= CONVERT(varchar(10), DATEADD(dd, -1, GETDATE()), 101)
AND
iAgentPerformanceStat.Timestamp<CONVERT(varchar(10), GETDATE(), 101))

That formula works, gives me last day.

Hope this help, I still cannot give the date I want.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 34961813
How about trying

(iAgentPerformanceStat.Timestamp>= CONVERT(varchar(10), DATEADD(dd, -1, {?Date}), 101)
AND
iAgentPerformanceStat.Timestamp<CONVERT(varchar(10), {?Date}, 101))


mlmcc
0
 
LVL 1

Assisted Solution

by:Etdashou
Etdashou earned 0 total points
ID: 34962957
Ok nice one mlmcc, it was almost it but you helped me fin the good one , here it is :

(iAgentPerformanceStat.Timestamp>=CONVERT(varchar(10), {?Date}, 101)
AND
iAgentPerformanceStat.Timestamp< CONVERT(varchar(10), DATEADD(dd, +1, {?Date}), 101))

*the only difference is that your query was looking at the day beofre the parameter, and mine is looking at the date of the paramater.

Hope this will help others as well
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34963282
Wasn't sure what date you were looking for.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 34968507
Interesting.  Assuming that the 101 format in Sybase gives you the date without the time, then it seems odd that you would have to do that.  In theory, if you converted the field and the parameter to strings, as in your original post, then they should match.

 About the only thing that I can think of is if maybe time zones are coming into play somehow?  A datetime that was 02/23 12 AM in one time zone, would be 02/24 1 AM in the next time zone.  I don't really see how/why time zones would be coming into play here, but I can't think of any other reason that converting the datetime field and date parameter to mm/dd/yyyy strings wouldn't work.

 IAC, I'm glad that you found a solution.

 James
0
 
LVL 1

Author Closing Comment

by:Etdashou
ID: 34995431
Both solutions are good depending of the what date you want to look at.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

801 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