Functions Int and Datediff

[IanWaterman]

I am working remotely from a client who is still on CR7. I have a sample of the MS SQL Data and I am building reports on CR8.5 and then saving as CR7.

In a formula I am using INT to return an integer and datediff to give me a difference in minutes bewteen two datetime fields.

It saves as CR7 and does not complain, however, when my client opens report in CR7 neither of these functions appear to be recognised, in that they are showing in black text instead of blue.

I do not have access to CR7 and would like to know if there are corresponding functions in CR which perform same actions as INT and DATEDIFF (in minutes).

If there are no such functions can some one suggest a way of performing these in CR7.

Thank you in advance for your help.

Ian

 

[rosemaryl]

Dear Ian,

I believe the datediff and integer funcitons were introduced in 8.0 so they wouldn't be available in a CR7 report. Crystal allows you to save a CR8.0 or 8.5 report as a CR7 report but won't support functions that do not exist.

However, since your client's db is MS SQL, you could use SQL Expressions to this instead. I believe SQL Expressions are supported in CR 7.0 ... it has been so long that I forget.

Try these two:

Steps: Insert/Field Object/SQL Expression/New, provide a name and enter:

(
datediff(n,startdate,enddate)
)

replace the startdate and enddate with the corresponding datetime field from the Report Fields pane to ensure correct syntax. Notice that in SQL server you do not put quotes around the interval.

The next piece should mimic Crystal's INT funciton.

(CAST(ROUND(123.678, 0) AS int))

Notes:

The open and close parens around the statements is required for SQL Server.

Use regular sql instead of the ODBC functions provided in the functions pane ...they are archaic and the syntax is not documented!

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

Try:

if instr({table.field},"."} > 0 then
val(left({table.field},instr({table.field},"."}-1))
else
{table.field}

As for the datediff function, that's avaliable as a SQL Server function so a SQL Expression or a SQL Server View would make more sense.

For SQL Expressions:

http://support.businessobjects.com/library/kbase/articles/c2002571.asp

There is also a date time difference UFL for old versions of Crystal:

http://support.businessobjects.com/communityCS/FilesAndUpdates/ufldtdif.exe.asp

-k

 

[lbass]

Or, I think you could use:

//for int:
round({table.number-.5)

//for datediff in minutes:
({table.date2}-{table.date1})*1440

-LB

 

[IanWaterman]

Thanks for all your help.

I eliminated the need for INT and converted to text and removed decimals.

I opted for the database view to create the date differences using the formula suggested by Rosemary.

I have been chasing my client to upgrade for ages hopefully this will be the spur.

Ian