Skip to Content
author's profile photo Former Member
Former Member

daysbetween function for two dates type NVARCHAR

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



Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jul 27, 2016 at 03:44 PM

    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,

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.