Substraction of Date is not working properly

[mhmmd_srf]

Hi All,

I have two data fields BusBreachDate_New and Stop time.

I want to subtract  BusBreachDate_New  -  Stop time , and check whether the result is +ve or -ve.

For one Incident, INC1010490, it coming wrong. Others are fine.

Could you please help me to understand. Not able to rectify.

Thanks,

Sarif

[surendraj]

Have you tried with interval

interval(Stop time, BusBreachDate_New)

[mhmmd_srf]

Yes I used interval only.

= interval(Timestamp(BusBreachDate_New,'MM/DD/YYYY hh:mm:ss') - Timestamp([Stop time],'MM/DD/YYYY hh:mm:ss'),'ss')

[Colin-Albert]

Your data is already correctly formatted as dates so this works.

interval( [Stop time]- BusBreachDate_New , 'D hh:mm')

[mhmmd_srf]

Sorry I have mistakenly make opposite of expression :

it should be :

BusBreachDate_New - StopTime.

I have rectified in my post.

The scenario I have posted, BusBreachDate_New is 07/05/2018  10:00:00 and StopTime is 07/05/2018 04:20:27.

Though BusBreachDate_New  is greater than StopTime, it is coming as negative.

This is my actual question.

Please check for Number = INC1010490.

Thanks in advance for your understanding.

Thanks,

Sarif

[Colin-Albert · Accepted solution]

You are manipulating the time format of the date field in the Final tab of the script to add 10:00:00 to the date format but are not amending the numeric value of the date. Date calculations are done on the numeric value of the date field not the formatted text.

IF(BusBreachDate_On_Holiday = 'YES' or CreateDate_On_Holiday = 'YES',
TIMESTAMP(BusBreachDateFormat + 1,'MM/DD/YYYY 10:00:00'),BreachTime_New1) AS BusBreachDate_New,

The num value of BusBreachDate_New  (43286) is for 07/05/2018 00:00:00  not 07/05/2018 10:00:00  hence the discrepancy in your calculation.  You need to add 10:00:00 to the field value not the format.

[mhmmd_srf]

Thanks a lot Colin. Now I understand the issue.

So I need to add this in script level, I am right?

I am using below script. Hope this will work. I will test and let you know, if any further help required.

 IF(BusBreachDate_On_Holiday = 'YES' or CreateDate_On_Holiday = 'YES',
TIMESTAMP(BusBreachDateFormat + 1,'MM/DD/YYYY')+10/24,BreachTime_New1) AS BusBreachDate_New