Using GREATEST(), LEAST(), And Date/Time Values With COALESCE() In MySQL

<!---
	When using GREATEST(), MySQL will return NULL if *any* of the arguments are NULL.
	Therefore, if it is plausible for an argument to be NULL, we need to use COALESCE()
	to ensure a value that is both non-NULL and, at the same time, will not corrupt the
	results (ie, will not be selected as the "result" over other valid values). When
	dealing with date / time values, using the empty string ('') will allow the real
	dates to come through in the result-set without performing unfortunate type coercion.
--->
<cfquery name="greatestDate" datasource="testing">
	SET @result = GREATEST(
		COALESCE(
			(
				SELECT
					UTC_TIMESTAMP()
				/*
				FROM
					some_table
				WHERE
					some_condition
				*/
			),
			''
		),
		COALESCE(
			(
				SELECT
					CAST( '2014/07/04' AS DATETIME ) -- Past date.
				/*
				FROM
					some_table
				WHERE
					some_condition
				*/
			),
			''
		),
		COALESCE(
			(
				SELECT
					NULL
				/*
				FROM
					some_table
				WHERE
					some_condition
				*/
			),
			''
		)
	);

	/*
		If none of the GREATEST() arguments returned a valid date / time value then
		our result will be the empty string. As such, we want to catch that case and
		return a NULL value to be a bit more "correct".
		--
		NOTE: In ColdFusion this doesn't really matter as NULL is translated into the
		empty string, implicitly, when dealing with the query object.
	*/
	SELECT
		NULLIF( @result, '' ) AS dateValue
	;
</cfquery>


<!--- Check to see if we have a real date to work with. --->
<cfif isDate( greatestDate.dateValue )>

	Date: <cfoutput>#dateFormat( greatestDate.dateValue, "mmmm d, yyyy" )#</cfoutput>

<cfelse>

	Date: <em>None could be found.</em>

</cfif>

<!---
	When using LEAST(), MySQL will return NULL if *any* of the arguments are NULL.
	Therefore, if it is plausible for an argument to be NULL, we need to use COALESCE()
	to ensure a value that is both non-NULL and, at the same time, on that will not
	corrupt the results (ie, will not be selected as the "result" over other valid
	values). When dealing with date / time values, using the max MySQL date / time value
	('9999-12-31 23:59:59') will allow the real dates to come through in the result-set
	without performing unfortunate type coercion.
--->
<cfquery name="leastDate" datasource="testing">
	SET @result = LEAST(
		COALESCE(
			(
				SELECT
					UTC_TIMESTAMP()
				/*
				FROM
					some_table
				WHERE
					some_condition
				*/
			),
			'9999-12-31 23:59:59'
		),
		COALESCE(
			(
				SELECT
					CAST( '2015/07/04' AS DATETIME ) -- Future date.
				/*
				FROM
					some_table
				WHERE
					some_condition
				*/
			),
			'9999-12-31 23:59:59'
		),
		COALESCE(
			(
				SELECT
					NULL
				/*
				FROM
					some_table
				WHERE
					some_condition
				*/
			),
			'9999-12-31 23:59:59'
		)
	);

	/*
		If none of the LEAST() arguments returned a valid date / time value then
		our result will be the max MySQL date. As such, we want to catch that case
		and return a NULL value so that our application doesn't think that the default
		value is what we intended.
	*/
	SELECT
		NULLIF( @result, '9999-12-31 23:59:59' ) AS dateValue
	;
</cfquery>


<!--- Check to see if we have a real date to work with. --->
<cfif isDate( leastDate.dateValue )>

	Date: <cfoutput>#dateFormat( leastDate.dateValue, "mmmm d, yyyy" )#</cfoutput>

<cfelse>

	Date: <em>None could be found.</em>

</cfif>