Background

Earlier this week I needed to determine the difference between two dates.

In Transact SQL, I will employ datediff.

In Transact SQL, datediff allows one to compare two dates using years, months, days, hours, minutes in terms of measurement.

Oracle

Sample

SQL

CREATE GLOBAL TEMPORARY TABLE friendBook
(
	  friendName VARCHAR2(100) NOT NULL
	, dateMet    DATE NOT NULL 
)
ON COMMIT PRESERVE ROWS;

/*
	Truncate Table
*/
TRUNCATE TABLE friendBook;

/*
	Add Data
*/
INSERT INTO friendBook
(
	  friendName
	, dateMet
)
SELECT

	  'Bob Wood'
	, TO_DATE('2023-01-01', 'YYYY-MM-DD')
	
FROM DUAL

UNION ALL

SELECT

	  'Steve Winwood'
	, TO_DATE('2023-02-01', 'YYYY-MM-DD')
	
FROM DUAL

UNION ALL

select

	  'Sam Niel'
	, SYSDATE

FROM DUAL 

UNION ALL

SELECT

	  'Terry Brahshaw'
	, TO_DATE('2023-02-07', 'YYYY-MM-DD')
	
FROM DUAL

;

/*
 * Get Data
*/
SELECT 
		  tblFB.friendName 
		  	AS "friend"
		  
		, tblFB.dateMet
		
		, TO_CHAR
			(
				SYSDATE
			) AS "nowUsingToChar"
		
		/*
		 * Convert SYSDATE to character using FORMAT
		 * FORMAT us YYYY-MM-DD HH:mi am/pm
		 * 
		*/	
		, TO_CHAR
			(
				   SYSDATE
				, 'YYYY-MM-DD  HH:mi pm'
			) AS "nowUsingToCharFormatted"		
		
		/*
		 * Days as return as fractional
		*/	
		, (
			SYSDATE
				- dateMet 
		  )
			AS "numberofDaysFractional"
			
		/*
		 * Days as return as fractional
		*/	
		,ROUND
			(
				  (
					SYSDATE
						- dateMet 
				  )
				  , 3
			)		  
			AS "numberofDaysFractional ( 3 dec. places )"
					
		
		/*
		 * Days as whole using Truncate function
		*/				
		, TRUNC
			(
				SYSDATE
					- dateMet
			) AS "numberofDaysWhole"		
		
		/*
		 * a full day in days is 1.0
		 * a full day in hours is 24 * fraction of SYSDATE - datemet
		*/	
		, TRUNC
			(
				24 * 
					( SYSDATE - dateMet )
					
			) AS "numberofHoursWhole"
		
FROM   friendBook tblFB

ORDER BY
	
		tblFB.dateMet ASC 
		
;



TRUNCATE TABLE friendBook;

DROP TABLE friendBook;

COMMIT;

Result

Grid

Image

Textual

Code Sharing

GitHub

Gist

 

Dedication

Borland Delphi

BTW, learnt how to calculate date differences using date arithmetic from Borland’s Delphi.

As such I am going to dedicate this post to the Borland Delphi Team.

Inclusive:-

1. Philippe Kahn
2. Anders Hejlsberg
   • Wikipedia
     Link
3. Danny Thorpe
   • Wikipedia
     Link
4. Lance Devin ( My Brother Lance )