BusinessObjects Board

T-SQL with Crystal Reports

building reports crystal reports
#1

I can’t seem to get this right in SQL. It works fine in Crystal, but I need to convert this into our SQL Server database.

This is the code:

if IsNull (datetimefieldend) then
dateadd (“d”,1,cdatetime(date(datetimefieldstart),time(235959)))
else
datetimefieldend

I’ve tried using convert, try_convert, concat, dateadd to no avail! Any suggestions/ideas?

#2

Use COALESCE logic:

COALESCE(datetimefieldend, dateadd (d,1,cdatetime(date(*datetimefieldstart* ),time(235959))))

This will return the first non-null value in the brackets and can take more than two arguments if required. What is your dateadd logic trying to achieve?

1 Like
#3

Hi MarkP,

Most of the logic you placed in there only works with Crystal and not T-SQL. I’m trying to convert it into SQL Server logic. The dateadd logic needs to be used in instances where it’s Null, it will bring in a full day which will be used in the logic to calculate the date difference, that’s a seperate piece of logic which I was able to translate.

#4

COALESCE is TSQL logic, I’ve been using it for years.

#5

Coalesce is, but I was referring to the remaining portion of the code.

This is essentially what we are trying to accomplish:

DateStart DateEnd Difference
10/02/2022 00:00:00 NULL N/A
10/01/2022 05:00:00 NULL N/A

We want the results to show the following:
10/02/2022 10/03/2022 24 hours
10/01/2022 05:00:00 10/02/2022 17 hours

So we do in fact want to bring in NULL values, but as described above.

#6

OK, let’s wind back to the start of the requirement then.
What field(s) do you have and what do you want to achieve in terms of data - for example, if End Date is null, show nulls else apply a calculation to it that does a difference in hours, etc.

#7

@indy - figured this out - cdatetime is a convert to date/time function in Crystal Reports.

So it looks like you want to add 1 second short of two days to the date part of the datetimefieldstart if it is null. Your overall logic would therefore be:

COALESCE(datetimefieldend, (DATEADD(SECOND, 172799 ,CAST(CAST(datetimefieldstart AS DATE) AS DATETIME)))

The 172799 is 1 second short of two days. The double cast is needed to first strip the time off then convert back to datetime to support the addition of the seconds. If you only wanted the same day just before midnight, use 86399 instead.

#8

Thank you MarkP, was able to get it, but your code is a little shorter :slight_smile: