asked on

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

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.

Mike McCracken

Are you building the query is the database or trying to use a Crystal Command?

mlmcc

James0628

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

Etdashou

ASKER

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.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Etdashou

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

Wasn't sure what date you were looking for.

mlmcc

James0628

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

Etdashou

ASKER

Both solutions are good depending of the what date you want to look at.