Format DATETIME

T-SQL is strong and efficient in data manipulation tasks, but with formatting
related tasks it is weak. For example, take a common task such as
formatting DATETIME values; the built-in support SQL Server has with the
CONVERT function is very rigid and limited, only allowing you to choose
among predefined style numbers. It doesn’t give you rich and flexible
options to define your own desired format string. I’ve seen too many T-
SQL implementations of UDFs and stored procedures containing dozens of
lines of code to achieve more flexible formatting of DATETIME values.
In areas such as formatting .NET is much richer, allowing for both much
simpler and more efficient implementation of a UDF that formats a
DATETIME value based on a given format string. Before I provide the
code for such a UDF, note that compared to built-in T-SQL functions like
CONVERT, a UDF (be it T-SQL or CLR) will incur a performance
penalty. But compared to a T-SQL implementation of such a UDF, a CLR
implementation is both simpler and faster.
Enough talk; here’s the CLR implementation of a UDF called
fn_format_datetime:

using System.Data.SqlTypes;public partial class UserDefinedFunctions{    [Microsoft.SqlServer.Server.SqlFunction]    public static SqlString fn_format_datetime(SqlDateTime dt, SqlString formatstring)    {        if (dt.IsNull | formatstring.IsNull)            return SqlString.Null;        else            return (SqlString)dt.Value.ToString(formatstring.Value);                 }};

As you can see, the code is amazingly simple, and all it does is invoking the
ToString method of the SqlDateTime datatype with a format string. That’s
all there is to it. I chose to return null on any null input, but of course you
could slightly revise the logic; for example, you could use some desirable
default format when the dt input is not null and formatstring is null.
If you don’t have experience with .NET development, you can find the
instructions to deploy the function in the databasehere.Once deployed in a
database, you can start using it in your T-SQL code. Note that the format
strings are case sensitive. For example, if you’re after a string with four digits
for the year, followed by two digits for the month, followed by two digits for
the day, you should use the format string 'yyyyMMdd' and not 'yyyymmdd'.
MM stands for two digits for the month part, while mm stands for two digits
for the minutes part.
See for yourself by running:

select   dbo.fn_format_datetime(GETDATE(), 'yyyyMMdd'),  dbo.fn_format_datetime(GETDATE(), 'yyyymmdd');

You can find details regarding date and time format strings here.

So simple and flexible!

Cheers,
--
BG