Background

Looking at some sample code.

The purpose of the decode function was lost on me until I looked at it more.

Code

WITH cteDate
(
	  "tsBegin"
	, "tsEnd"
)
as
(

	SELECT 
		  cast('01-JAN-2021' AS DATE) AS tsBegin
		, cast('01-JAN-2021' AS DATE) AS tsEnd
		
	FROM dual
	
	UNION ALL
	
	SELECT 
		  cast('01-JAN-2021' AS DATE) AS tsBegin
		, cast('02-JAN-2021' AS DATE) AS tsEnd
	
	FROM DUAL 

	UNION ALL
	
	SELECT 
		  cast('02-JAN-2021' AS DATE) AS tsBegin
		, cast('03-JAN-2021' AS DATE) AS tsEnd
	
	FROM DUAL 

	UNION ALL
	
	SELECT 
		  cast('31-JAN-2021' AS DATE) AS tsBegin
		, cast('31-JAN-2021' AS DATE) AS tsEnd
	
	FROM DUAL 

	UNION ALL
	
	SELECT 
		  CAST
		  (
		  	  
		  	  TO_DATE
		  	  (
		  	  	  '2021-02-14 11:10:23'
		  	  	, 'YYYY-MM-DD HH24:MI:SS'
	  	  	  )
	  	  	  
		       
              AS DATE
              
		 ) AS tsBegin
		 
		, cast('14-FEB-2021' AS DATE) AS tsEnd
	
	FROM DUAL 
		
)	
SELECT 
		  cteTS."tsBegin"
		
		, cteTS."tsEnd"
		
		  /*
		   * If begin date and end date are on
		   * the same day, then return 1
		   * else return 0
		   */
		, DECODE
			(
				  trunc(cteTS."tsBegin")
				, trunc(cteTS."tsEnd")
				, 1
				, 0
			) AS "sameDay"
			

FROM   cteDate cteTS

Output

Source Code

Gist

DanielAdeniji/OracleDecodeCompareDates.sql

Link

Summary

Took to the lab and confirmed that decode was simply used to compare if the first two values are same.

If same, return 1.

Else return 0.

References

1. Oracle
   • Database SQL Reference
     • TRUNC (date)
       Link