Formatting a DateTime as text in SQL Server

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


Share Tweet Send
0 Comments
Loading...
You've successfully subscribed to SimonReynolds.ie
Great! Next, complete checkout for full access to SimonReynolds.ie
Welcome back! You've successfully signed in
Success! Your account is fully activated, you now have access to all content.