Date/Time formatting trick for SQL Server

As we all know, ColdFusion has a stellar line up of date and time formatting functions. But what happens when you need to do this at the database query level in a CFC or a Stored Procedure? Plus a FREE Scalar function!

So here's a quick way using SQL Server's CONVERT function to produce a MM/DD/YYYY HH:MM AM type of format (example output: 05/10/2010 03:17 PM) directly in your SELECT statement...

This can prove to be useful if you want to handle the date and time formatting within a cfquery or maybe even inside of a T-SQL Stored Procedure that you'll call via ColdFusion. If you want to get downright modular and really fancy (and who doesn't?) then you could create your own SCALAR function in SQL Server and just call it inline directly in a SELECT statement. Here, just run this from a T-SQL prompt and you too can call it right from your very own cfquery! (Don't say I never gave you anything ;)

So now that we've done the heavy lifting up front we can use this in any query just as if it were an out of the box SQL Server function - which means that you can also use it all over the place in ColdFusion cfquery statements too - WOOT!

Remember folks, FUNCTIONS are your friends!