Problem of SQL Server Ignoring seconds and milisecons at the time of implicit conversion

Hi Guys,

SQL server has the feature of implicit conversion of data types at the time of inserting records into a table.

I want to take your attention here on a specific issue related to varchar to datetime data type conversion.

When we insert data into a DATETIME or SMALLDATETIME column, SQL Server automatically attempts to convert the data if it is of a different type. 

For example, if you insert a varchar value into a DATETIME column, SQL Server will convert the data – if the value is in an  acceptable format.

Sometimes you will notice that this implicit conversion has ignored the values of seconds and milisecons, and inserted zero instead.To cope up with this situation you need to explicitly convert the field value using Convert or Cast function.

for more study on this you can refer to http://searchsqlserver.techtarget.com/tip/SQL-Server-data-conversions-from-date-time-values-to-character-types .