How to format a Date or DateTime in SQL Server

Question: How do you create a DateTime in a specific format in SQL Server?

Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. This means that it is no longer a DateTime. It is a VARCHAR.

This might not be the answer you are looking for, but please ... don't stop reading!

. . .

Why do people have trouble with the concept of raw data versus the presentation of that data? Am I missing something? If you use CONVERT() in an attempt format your DateTime data, surely people must understand that CONVERT() also converts that data from a DateTime to another data type? It's right there in the expression:

SELECT CONVERT(varchar(10), someDate)

That clearly is CONVERTing someDate to a varchar(10), isn't it? What gets returned when you convert something to a VARCHAR, a date or a string? The answer is a string of meaningless characters that no longer have any value as an actual date.

"You clearly are a hack, Jeff," you tell me, "since it is very easy for a SQL-Master such as myself to format dates without converting them to strings. For example, check this out this sweet 'mm/dd/yyyy' format:

select right('0' + rtrim(month(@d)),2) + '/' + right('0' + rtrim(day(@d)),2) + '/' + rtrim(year(@d)).

I can write stuff like this all week! It's easy. You've got much to learn!"

Well, I have bad news for you. That still is implicitly converting everything to a string -- the rtrim() function is handling that part. This is even worse than doing it explicitly with a CONVERT() function, and the end result is not easy to read or work with or write, it is not efficient, and it is still not returning a DateTime value.

. . .

Always remember: If a value is not a DateTime datatype, it is not a date. No matter what it looks like, or how neatly formatted you made that string, or how careful you were to use an ISO compliant format, it is not a Date. Period.

It is crucial to understand this, and to thus to understand the implications of trying to "format" data at the database layer. It cannot be done! All you can do is convert things to generic "string" datatypes. That's it.

. . .

No matter what they may look like, strings don't sort like dates. They don't compare like dates. You can't get the month from a string consistently, or calculate the amount of minutes between two strings, or add x days to a string. You can't ensure that different databases or stored procedures or functions or applications will always interpret your chosen date formatted string the same. Client applications -- who should be doing the formatting -- cannot apply date formatting to a string, they need an actual date stored in the correct data type. Thus, they would need to convert this string back to a DateTime type and only then can they format it for display purposes or use standard date calculations on the value. Does it really make sense to start with a date value, convert it to a string in SQL, and then have your client convert it back to a date value?

Simply return raw data from your database using the proper data types, and then simply use the tools designed to handle raw data in the correct types at your clients to format and present that data.

* In crystal reports or other reporting tools, you can just drop your nice, clean, raw unformatted datetime value on your report, right-click it, and easily format it any way you want. You can use regional settings, specify mm/dd/yyyy format strings, and all kinds of options. It's simple and easy, but you must return datetime values back from SQL, not VARCHARS!
* In Excel, again, you can simply right-click and choose any format you want, or create your own.
* In .NET applications, you can usually format dates in data bound controls using the GUI interface, and you can also format things using the ToString() method of a true datetime value and specify all kinds of simple yet flexible formatting strings.
* In ASP.NET web pages, just about all data bound web controls let you specify a FormatString property for your bound columns, giving you clear, simple control over exactly how your dates look.
* In VB and VBA, there is a Format() function that again works with named formats or custom format strings.
* In MS Access, the report and form designer lets you format any text box containing a datetime value any way you want, again with simple format names or format strings, and you have all of the VBA functions available to format dates in your code. You can even specify the specific date format for columns in a query in the query designer -- but, again, you must be working with data in the correct datetime data type.

Isn't it much easier to simply right-click on something and then enter a simple "mmm dd, yyyy" format string instead of building and parsing this manually using CONVERT and SUBSTRING parsing in T-SQL? Isn't it more flexible to do all formatting at your presentation layer so that you can just return data from your database and not worry about how it looks? Then 5 different clients can query the same stored procedure and each output those dates any way they want -- without changing any database code.