Date formula not in SQL 1

[DataDog]

CR 8.5
CE 8.5
MS SQL DB

As I understand it, SQL Server doesn't have a "Date" field, only a "DateTime" field. (at any rate, the field in question is a "DateTime&quotTherfore, I set up a formula to extract the Date portion.

//@DateFormula
Date(IndexDateTime)

Add 2 Parameters (formatted as Date) for ?Start and ?End.
Point Select Expert to @DateFormula to be between ?Start and ?End.

When I go to Show SQL ... NOTHING shows up regarding the date. The Report is EXTREMLY slow.

What am I missing?

 

[synapsevampire]

Do the reverse.

Create formulas which conbvert your date parms to datetime type, and reference the formulas in the record selection formula.

Formulas:

@start
DateTime(Year({?start}),month({?start}),day({?start}),0,0,0)
@end
DateTime(Year({?end}),month({?end}),day({?end}),23,59,59)

Record Selection formula:

{Table.datefield} >= {@start}
and
{Table.datefield} <= {@end}

This will convert and pass the SQL.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear DataDog,

Not to muck around with SV's formula and solution (hi SV). I do selects against a SQL db all the time and this will return data for dates between the start and end parameters with no need to convert the datetime type and it passes the SQL:

The parameters are as you stated, Date Types.

{Table.Datefield} in {?Start} to {?End}

The SQL that is generated automatically compensates for the datetime issues.

Maybe I am not understanding your issue, but this works just fine for me.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[DataDog]

Hi Rosemary

Like you, my Date formulas work ... they were just VERY slow!

synapsevampire: YOU ARE DA MAN!

I told you something wrong yesterday, Sunday,( I didn't have access to Crystal). The field in my database is formatted as a STRING that LOOKS LIKE a DateTime (2002-12-01 12:13:14&quot;.

BUT, using your logic:
//@New Scan Date Start
totext(date(year({?Start Date for Report}),month({?Start Date for Report}),
day({?Start Date for Report})),&quot;yyyy-MM-dd&quot; )

//@New Scan Date End
totext(date(year({?End Date for Report}),month({?End Date for Report}),
day({?End Date for Report}))+1,&quot;yyyy-MM-dd&quot; )
//Note:I had to add &quot;+1&quot; day//

Select Expert Formula
({EB079.SCAN_STRT_DT})>({@New Scan Date Start}) and
({EB079.SCAN_STRT_DT})<({@New Scan Date End})

Resulted in SQL:
WHERE
EB079_18.&quot;INDEX_DT&quot; > '2003-01-01' AND
EB079_18.&quot;INDEX_DT&quot; < '2003-02-01'

RATHER THAN THE REPORT RUNNING FOR 7 MINUTES, IT NOW RUNS IN 15 SECONDS!

YOU DEFINETLY GET A STAR FOR THIS ONE!!!!!

THANKS AGAIN!

 

[rosemaryl]

Dear Datadog,

I am surprised that you find them slow. I wouldn't tolerate a report taking 7 minutes to run attibutable to a simple date selection. If my dates pass to SQL then I can return 100,000 records (help desk calls for example with linked tables) on a report between two date ranges in mere seconds.

I am very happy that SV's formula worked for you - He is very very good and I am happy that he contributes as much as he does here at Tek-Tips (I don't know where you find the time SV <smile>).

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[DataDog]

Dear Rosemary:

Thanks to &quot;SV&quot;( crNinja formally known as synapsevampire), I now understand the problem.
It's this: If you have a database field that is a Date or DateTime BUT set up in the database as a STRING, in order for the SQL to be passed, you MUST give the SQL a STRING.
An example of this is when the database = 20020201 and the ?Parameter = 02/01/2002. (much more user friendly), In order for the parameter to be passed, you MUST have a formula in the Select Expert to convert ?Parameter 02/01/2002 (date formatted) to 20020201 (String formatted).
I understand that if the Database is a Date and the Parameter is a Date ,you don't have to go through this junk.
We only have about 40 Enterprise reports out there right now that we have to fix. My company just bought 50 Crystal Enterprise licenses (unlimited Users) (yup .. mega $$$) so the timing on this couldn't be better.

 

[rosemaryl]

Dear Datadog,

You should look at turning SV's formula into a SQL expression which will be even faster.

Or better yet, if you can get the dba to do this or if you have access, create a field in your database that does the same thing.

Or if he/she is opposed to that, creating a User Defined Function that will do the convert (the parameter to pass is any table.stringdatefield). This way it is usuable for any of these string date fields in your entire database and not specific to any one. This becomes reusable code! You may not know that you can call functions in SQL Expressions, I do this all the time and it is really fast.

An example of calling a udf in a SQL Expression:

(dbo.myudf('Table.AnyStringDateField'))

Just make sure that you or whoever creates it for you grants execute on the User Defined Function to Public.

Good luck... I like the CRNinja reference for SV, it seems apt somehow! <smile>

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.