Data type conversion is very frequent and common practices with data to manipulate or reproduce data. As we regularly use conversion functions CONVERT and CAST in our daily routine.
Sometime its very needed to convert any datetime value to string or Date string to Datetime.
Let see example below for Datetime to character string conversion
Suppose if it required to convert a date string to date/Datetime, usually we use
But now if milliseconds part is more than 3 digits, it will give error message as
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
To resolve this, new data type DATETIME2 introduced from SQL Server 2008 onwards and beauty of DATETIME2 is that its supports fractional precision upto 7 digit. Let See examples
WikiDBA 