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'
The full list of format codes for both
CONVERT can be found here