OracleDataDiffFriendBook.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 Bradshaw'
	, 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;