Format Dates in SQL Server like C# and VB.net

Search is a big part of the functionality built into our web applications and datetime data types are one of the more difficult types to search because the data is stored differently than it is displayed. We use ASP.net and C# as part of our arsenal of development tools and many times we need to search the database in the same format as the user is seeing it. So for a while now we have been wishing there was a way to do something like this: