date minus with another date

[inam]

Hello community ;

please help me for below query.

input:-

BCD	DEPDATE & TIME
23-07-2021	2021-08-12 - 1705 / 2021-08-12 - 2030
28-03-2022	2022-04-06 - 1340
11-01-2023	2022-11-21 - 0645
06-01-2023	2022-07-12 - 0000 / 2022-7-12 - 0000

EXPECTED OUTPUT:-

BCD	DEPDATE & TIME				day(bcd-depate & time)
21-12-2021	2021-12-26 - 1705 / 2021-12-30 - 2030				9
28-03-2022	2022-04-06 - 1340				9
11-01-2023	2022-11-21 - 0645				-51

 I WANT:-

1) I want BCD - DEPDATE & TIME

2) if getting negative number replace negative number with zero(0) like -51 replace with zero.

3) BCD - (LAST DATE OF DEPDATE & TIME  (Right side) )

4) I used this  query but didn't get answer.

  date(Right([DEPDATE &TIME],10),'DD-MM-YYYY')- date([BOOKING CREATION DATE],'DD-MM-YYYY') as days,

if i change (Right) with (left) i get answer  but  they minus left side date 

[Marijn]

floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm'))
-
floor(date#([BCD],'DD-MM-YYYY'))

[inam]

I apply the logic but day(bcd-depate & time) column is blank. not getting any values in day(bcd-depate & time) column

[inam]

its working now but how to set 0 if i found negative number.

[Marijn]

if(
      floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm')) - floor(date#([BCD],'DD-MM-YYYY')) < 0,0,
      floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm')) -
      floor(date#([BCD],'DD-MM-YYYY'))

)

[inam]

Thanks A lot .its working fine but i have one doubt

some time my depdate & time is : like (13:00) or some time is like(1300) i..so i write 17 or 18 in the code 

BCD	DEPDATE & TIME		days
13-02-2023	11-02-2023 - 0630		-2 but i get (0) (correct according to query)
03-03-2023	2023-03-01 - 13:00		but here date is not 0 beacuse of this  colon 13:00 (here total length is 18 not 17)

how i solve this type  format  beacuse some times i getting colon and sometimes not .

[Marijn]

if(
      floor(date#(right(purgechar[(DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) - floor(date#([BCD],'DD-MM-YYYY')) < 0,0,
      floor(date#(right(purgechar([DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) -
      floor(date#([BCD],'DD-MM-YYYY'))

)

[Marijn]

if(
      floor(date#(right(purgechar[(DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) - floor(date#([BCD],'DD-MM-YYYY')) < 0,0,
      floor(date#(right(purgechar([DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) -
      floor(date#([BCD],'DD-MM-YYYY'))

)

[inam]

Thanks marijn 😊

[igizarblehic]

Thanks for sharing your knowledge.

ballsportsgames