Need Help With SQL Expression

[Nuffsaid]

Hi All

We have this Sql statement which converts real dates into julian dates. I would like to incorporate this statement into my report so a user could enter a date into a parameter field and the sql expression would convert it, then pass it down to the server as a julian date.

Although this statement works in our SQL I don't know if the syntax is correct for an ODBC connection. (I use ODBC to connect Crystal to our database)

Here's the sql statement;

/* Convert from real date to julian date */

declare @realdate datetime
select @realdate = 'mm/dd/yyyy' --(this would be the Parameter)
select datediff(dd, "1/1/1753", @realdate) + 639906

I know this statement uses variables and I'm not sure if this can be done from within Crystal.

Any ideas ?????

Thx

Nuffsaid.

 

[rhinok]

Sorry Nuff, this can't be done with a SQL Expression:-(

SQL Expressions can't interact with non-database fields such as parameters or formulas. You could still accomplish this goal via formulas, but as you've noticed, it won't be passed to the server which means it will be processed client-side. Hopefully, you have other criteria in your record selection statement that would make this as painless as possible.

If not, perhaps you can create a Crystal Query and base the report off that?

 

[kenhamady]

Can you make this expression part of a stored procedure or view and report off of that? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Nuffsaid]

Hi Guys,

Thanks for your input.

I was afraid of this....

As I'm reporting off a Data Warehouse my next option is to have this conversion done "Server Side". (That's no big deal, my DBA just loves me!!)

Although I do have other crieria, date ranges are what really "speed up" the report.

Who is this guy "Julian" anyway ???????

Thx

Nuffsaid.

 

[Ngolem]

NuffSaid

"datediff(dd, "1/1/1753", @realdate) + 639906"


Why can't this be a formula??? presumably you are calculating the formual, @realdate, somewhere why can't you continue that formula and calculate Julian time as well???

Jim

 

[Nuffsaid]

Ngolem,

When you say "formula" I assume you mean Crystal formula? If so, the goal is to have the processing done server side. I'm leaning towards Ken's idea about a Sp. I've read that an Sp can prompt the user for a parameter, just trying to figure out if this would be of any use. Crystal states that a report based on an sp with a parameter, will prompt the user for the parameter. Might be OK within the designer, but how will it react when it's published to Enterprise? Am I chasing a ghost??? Maybe I should just stick with my existing Ufl that does a great conversion job, client side, and let the IT guys worry about network traffic.

I'll let you know what happens......

Nuffsaid.

 

[kenhamady]

I have never tried it, but Enterprise should handle the SP parameter if CR does. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[chelseatech]

I've got a client site using Stored Procedures on a Oracle 8 Db and CE std - works like a charm. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Nuffsaid]

Hi,

They don’t want to handle the traffic, so here’s what’s been decided. If they have a SQL statement that converts a “real date” to julian then they can work it backward and convert julian to real. So we’ll add another field to the database table which converts the julian date to a real date. Problem solved!

Sometimes you just have to be in with the help...

Nuffsaid.