SQL

Formatting a DateTime as text in SQL Server

Simon Reynolds

Simon Reynolds

— 1 min read
Formatting a DateTime as text in SQL Server

I'm currently trying to display a Telerik Grid in an MVC view, one of the columns is a nullable DateTime2. The grid is using client side sorting as it doesn''t have to deal with large enough amounts of data to be worth implementing server-side sorting, filtering etc.

Naturally, client side sorting and nullable values do not play well together. Because of course they don't. The data source is a plain C# class populated by a stored procedure so a nullable VARCHAR column can be easily taken care of within the procedure by using

SELECT
a.MandatoryString,
COALESCE(a.OptionalString, '') as OptionalStringThatWillNeverBeNull
FROM Table
WHERE /* ... */

We can't use the same trick with a DateTime2 though as coalescing it with an empty string will result in a value of '1900-01-01 00:00:00.0000000'

Instead we can do the following

SELECT
a.MandatoryString,
COALESCE(a.OptionalString, '') as OptionalStringThatWillNeverBeNull,
CASE WHEN a.OptionalDate IS NULL
THEN ''
ELSE CONVERT(VARCHAR(20), a.OptionalDate, 126)
END AS OptionalDateAsStringThatWillNeverBeNull ,
FROM Table
WHERE /* ... */

This converts the DateTime2 to text, formatting it with a particular style. The style is specified by the past parameter. In the example above, 126 is the format code for ISO8601.
This gives us a string which will either be empty or which can be parsed correctly to a date for display. A date of October 21st 2014 at 04:47 AM will be formatted as '2014-10-21T04:47:00'

In this instance the ISO format was chosen as it can then be parsed by both C# on the server and javascript on the client as required

The full list of format codes for both CAST and CONVERT can be found here

Read more