Converting Milliseconds/Nanoseconds date string values to Date/Datetime

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

SQL Server’s Error: System assertion check has failed

Yesterday my one team guy came to me with an error as

SPID:         XX
Process ID:   XXXX
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

When we checked DBCC CHECKDB, results were as
DBCC CHECKDB WITH NO_INFOMSGS – reported no problems.
DBCC CHECKDB, report 0 errors, 0 consistency errors.

After analyzing queries which he was using, we came to know there was 4-5 joins were used and in two tables a comparing columns was different data types as INT and BIGINT. After changing INT to BIGINT our problem got resolved.