tim_harkin (8/27/2015)
Jeff Moden (8/19/2015)
There are very few places in SQL Server that have nearly absolute rules but one of them is that you should never store dates or times in any kind of character based or numeric type of column. You should be using one of the date/time datatypes instead.
Heh... now let me guess. Is the current column NVARCHAR(256)?
Off topic war stories...
I worked on an app for a while that stored dates as sql_varient. A start and end date would be entered on a web form, and then then elapsed time would be calculated by parsing the dates out of the varient field. The developers loved to claim that the database was slow.
One could argue that typing a date/time attribute as anything other than a standardized Date or Time type is a form of de-normalization. What it's doing is coding a non-conformed textual description of a date/time rather than it's true binary key value. It would be like coding "John K Smith" and/or "SMITH, JOHN K" in a PurchaseOrder table rather than CustomerID.
If we find ourselves in a situation where we must support a legacy database that contains dates in text format, then two options for at least conforming the coding of dates would be a check constraint (like the one I described above in an earlier post) or perhaps a foreign key constraint referencing a calendar table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho