sql. syntax select Perform Arithmetic Operations

Question

i want Perform Arithmetic Operations in select syntac sql like 10 minus 5 = 5 but my select not work.

select DATEDIFF(MINUTE,on_duty, clock_in ) as late, DATEDIFF(MINUTE ,off_duty, clock_out ) as Early, late - Early as jajalan from kkpsurabaya

look image pls

Answer 1

You cannot use the result of the function in the same select because you cannot refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. You could call the same function multiple times:

select DATEDIFF(MINUTE,on_duty, clock_in ) as late, 
       DATEDIFF(MINUTE ,off_duty, clock_out ) as Early, 
       DATEDIFF(MINUTE,on_duty, clock_in )- DATEDIFF(MINUTE ,off_duty, clock_out ) as jajalan 
from kkpsurabaya

or a common-table-expreession:

WITH CTE AS
(
    SELECT DATEDIFF(MINUTE,on_duty, clock_in ) as Late,
    DATEDIFF(MINUTE ,off_duty, clock_out ) as Early,
    t.*
    FROM kkpsurabaya t  
)
SELECT Late, Early, Late - Early as jajalan
FROM CTE