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

  1. Gyaneshwar says:

    what is basic unit or instant for time?


  2. It may be measured till nanoseconds.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s