daysbetween function for two dates type NVARCHAR

[Former Member]

Hi All,

I made a calculation view , and I have  2 columns:

1.  BUDAT  (posting date )  type NVARCHAR8

2.  BLDAT(document date) type NVARCHAR 8

I need to create 1 calculated  column which calculates the difference between these two dates.

My approach:

New calculated column: ZDAYGAP  type NVARCHAR6

Expression :

if("BUDAT" != ' ' and "BLDAT" != ' ',string(daysbetween(date("BUDAT"),date("BLDAT"))), ' ')

But it is not giving any result. Always showing result 0.

What is the correct approach for this

Reagrds

Meenu

[former_member685848 · Accepted solution]

Hello Meenu,

Your daysbetween statement looks right.

I understand that fields BUDAT and BLDAT are NVARCHAR(8) with this format: YYYYMMDD (example: 20160727).

In that case, daysbetween( date( BUDAT ), date ( BLDAT) ) should work.

Now, you are trying to execute an exception treatment for when BUDAT or BLDAT are null?

Not sure if this is causing your problem, but try this instead:

1) For check if the fields have value try isNull(BUDAT) OR isNULL (BLDAT) THEN 0 ELSE daysbetween

Something like that:

IF (isNull(BUDAT) OR isNull(BLDAT) , 0, daysbetween( date( BUDAT ), date ( BLDAT) ) )

I also would not convert result for string in formula, you can do this just selecting string as this calculated column type, but maybe this should be decimal or integer.

Best regards,

[Former Member · Accepted solution]

Hi Andre,

Thanks for response.

I tried like:

IF(isnull("BUDAT") OR isnull("BLDAT"),

0,

daysbetween(date("BLDAT"),date("BUDAT"))

)

This gives no syntax error. But results are like:

it is not checking  the isnull expression.

[former_member685848 · Accepted solution]

Ok, so we are not facing null data (represented by ?) and not blank string (represented by ' ' or '').

Did you already try '' or only ' '?

What about use the big guns?

just print strlen("BUDAT") and let we know if these blank values has 0 or 1 lenght.

Then you could just replace isNull statement for strlen("BUDAT") = 8 AND strlen("BLDAT") = 8 and switch THEN and ELSE statements.

Something like that:

IF (strlen("BUDAT") = 8 AND strlen("BLDAT") = 8 , daysbetween( date( "BUDAT" ), date ( "BLDAT") ), 0 )

Regards,

[former_member182302 · Accepted solution]

It is because BUDAT was not NULL and it was a blank space ( ' ' ) .

Try with the below logic  and let us know if it works :

IF(isnull("BUDAT")  OR "BUDAT" = ' ' OR isnull("BLDAT") OR "BLDAT" = ' ' ,
0,
daysbetween(date("BLDAT"),date("BUDAT"))
)

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Andre,

This formula given by you , works for me . Thanks for it.

IF (strlen("BUDAT") = 8 AND strlen("BLDAT") = 8 , daysbetween( date( "BUDAT" ), date ( "BLDAT") ), 0 )

But now only 1 concern if left, if dates are blank then in that case the result field should be blank not 0.

right now its 0.

If i write formula like:

IF (strlen("BUDAT") = 8 AND strlen("BLDAT") = 8 , daysbetween( date( "BUDAT" ), date ( "BLDAT") ), '  ' )

then the expression is not valid

[former_member182302 · Accepted solution]

Because ' ' is character, so it leads to inconsistentency that formula is returning integer in some cases and chsracter in other. Try keeping the calculated column : data type as VARCHAR and let us know

Regards

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

I tried with  column type as varchar.

still it is showing error.

[former_member182302 · Accepted solution]

Try like below :

Result :

Regards,

Krishna Tangudu