Ask Ben: Averaging Date/Time Stamps In SQL

code-1.sql

CAST( date_time_value AS FLOAT )

code-2.cfm

<cfquery name="qFullAverage" datasource="..." username="..." password="...">
	SELECT
		CAST(
			AVG( 
				CAST( date_created AS FLOAT ) 
			) 
			AS DATETIME 
		)
	FROM
		web_stats_session
</cfquery>

code-3.cfm

<cfquery name="qDateOnlyAverage" datasource="..." username="..." password="...">
	SELECT
		CAST(
			FLOOR(
				AVG( 
					-- This inner FLOOR() call is optional (I think).
					-- The averaging of the integer parts should
					-- not be affected. Put it in if you want.
					-- FLOOR( 
						CAST( date_created AS FLOAT ) 
					--)
				) 
			)
			AS DATETIME
		)
	FROM
		web_stats_session
</cfquery>

code-4.cfm

<cfquery name="qTimeOnlyAverage" datasource="..." username="..." password="...">
	SELECT
		CAST(
			AVG(
				-- Get full floating point value.
				CAST( date_created AS FLOAT ) - 
				-- Subtract the integer part.
				FLOOR( 
					CAST( date_created AS FLOAT ) 
				) 
			) 
			AS DATETIME
		)
	FROM
		web_stats_session
</cfquery>