Technical Questions: Date conversion

Date conversion

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14641
Printed Date: 19 May 2024 at 9:09am

Topic: Date conversion

Posted By: Zuzanna
Subject: Date conversion
Date Posted: 11 Oct 2011 at 10:24am

Hi,

I have a formula as follows

ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-{SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY})/365* mailto:%7b@BASEHOURS%7d,2 - {@BASEHOURS},2 )

This formula should subtract 2 dates (EXP9_2 is a prompt date eg. Oct-1-2011) followed by the other calculation. The problem is that, sometimes the second date LHS_DT_ORIG_SENRTY is null and the result is blank. In this case I want the calculation to use Oct-1-2011 div 365 mult base hrs. I tried to use the nvl function in the SQL query nvl(E.LHS_DT_ORIG_SENRTY, '0000/00/00') thinking that it will use zero in crystal formula, but it doesn't work, result blank. I also tried to use isdate({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) to test for null, but it's giving me formula error saying that is should be a string.

Can anyone help ? Thanks in advance.

Replies:

Posted By: DBlank
Date Posted: 11 Oct 2011 at 10:48am

maybe...

Round({SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-(ifisnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then date(2011,10,1) else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}))/365* mailto:%7b@BASEHOURS%7d,2 - {@BASEHOURS},2 )

Posted By: Zuzanna
Date Posted: 13 Oct 2011 at 4:51am

Hi,

I didn't explain it properly. What I really want is that if in the following formula

ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-{SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY})/365* mailto:%7b@BASEHOURS%7d,2 - the LHS_DT_ORIG_SENRTY is null, I want to use zero in the subtraction from EXP9_2. So if the EXP9_2 prompt date equals to eg.Oct-1-2011 and the LHS_DT_ORIG_SENRTY is null, I want to subtract 0 so the result of the subtraction is whatever date is in EXP9_2.

Also I tried the suggested method, but I keep getting syntax error

"The ) is missing" and it highlights the ifisnull. I tried to fix it, but it didn't work.

Thanks.

Posted By: DBlank
Date Posted: 13 Oct 2011 at 5:07am

missed a space in the ifisnull, should be "if isnull".
I assumed you were using date fields but now it looks like numeric fields.
IF that is correct you can either chaneg the fomrual to use defualt values for nulls (pick list option in the formula manager or chaneg the formula as

ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-(if isnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then 0 else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY})/365* mailto:%7b@BASEHOURS%7d,2 - {@BASEHOURS},2 )

Posted By: Zuzanna
Date Posted: 13 Oct 2011 at 5:22am

Hi,

now I am getting an error "A number is required here"

ROUND(( {SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-(if isnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then 0 else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}))/365* mailto:%7b@BASEHOURS%7d,2 - {@BASEHOURS},2 )

EXP9_2 is a date and {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY} is also a date.

I don't know how to get around the formula to calculate the balance if LHS_DT_ORIG_SENRTY date fiels is null.

Thanks.

Posted By: DBlank
Date Posted: 13 Oct 2011 at 5:43am

I thought these were dates,

So you want the date difference in days between EXP9 and LHS_DT_orig_sentry, correct?

if the LHS_DT_orig_sentry is NULL you can't use zero. So do you want the days from today? or alwasy 0 for the whole thing? or seomething else?

Posted By: Zuzanna
Date Posted: 13 Oct 2011 at 8:48am

Hi,

I see what you mean, if I have 2 dates

EXP9 - 2011/10/04 and LHS_DT_orig_sentry - 2006/10/02 and I subtract them in the formula as dates and display the result on the report, I get date difference 1,828. I can do the same manually in excel by converting 2011/10/04 to number 40,820 and 2006/10/02 to number 38,992. I think excel does the conversion starting with Jan/1/1900. But if LHS_DT_orig_sentry is blank, I just want to use the 2011/10/02 converted to number 40,820, so I can use it in my balance calculation. Can you convert date to a number in crystal or is there another solution ?

Posted By: DBlank
Date Posted: 13 Oct 2011 at 9:23am

I think you want to use today if it isnull.

if you jsut used the numeric day value it is not coming close to any comparitive value of you date difference.

try it and see if you get tha values you expected...

Round({SJ_SENIORITY_INFO_BY_EMPLOYEE.EXP9_2}-(if isnull({SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}) then currentdate else {SJ_SENIORITY_INFO_BY_EMPLOYEE.LHS_DT_ORIG_SENRTY}))/365* mailto:%7b@BASEHOURS%7d,2 - {@BASEHOURS},2 )

Posted By: Zuzanna
Date Posted: 14 Oct 2011 at 8:29am

Hi,

luckily it turned out to be invalid data issue. Thanks for all your guidance.