I've learned many tricks over the years for dealing with dates in SQL Server. Today, one of my fellow developers came up to me and asked me how to handle string concatenation and, as it turned out, what he was trying to do was to get yesterday's date for filtering.

Fortunately, this is something I've done a time or two before so I showed him this quick trick:

select convert(datetime,convert(char(10),getdate()-1,101))

The innermost conversion changes the date to a truncated string of MM/DD/YYYY, which conviently removes any time and resets the date to Midnight. GetDate()-1 subtracts one day, another handy feature. Finally, just because SQL Server would do it itself but not necessarily as efficiently, convert the string to a date time. Which effectively changes 6/10/2008 16:08 to 6/10/2008.