simflex-897410 (8/25/2013)
However, the issue we have now is that when we try to update same date value, it stores a different formatted value like:
And now you understand why storing a formatted date of any kind is one of the worst database sins there is. Store all dates using one of the date/time or date datatypes and format only when consumed. If you're really pressed, create a persisted calculated column to do the formatting for you but don't store the actual data as a formatted date/time of any type.
As a bit of a sidebar, the app should check what the user has entered to see if it can even be converted to a date and, if it can, IT should pass the date as a datetime or date datatype. If it's not a valid date, the app should alert the user without even touching the database.
--Jeff Moden
Change is inevitable... Change for the better is not.