What are dirty reads in SQL Server

 Dirty reads (or uncommitted reads) are reads of rows which are being modified by an open transaction.

This behavior can be replicated by using 2 separate queries: one to open a transaction and write some data to a table without committing, the other to select the data to be written (but not yet committed) with this isolation level.

Query 1 - Prepare a transaction but do not finish it:

CREATE TABLE dbo.demo (
    col1 INT,
    col2 VARCHAR(255)
);
GO
--This row will get committed normally:
BEGIN TRANSACTION;
INSERT INTO dbo.demo(col1, col2)
    VALUES (99, 'Normal transaction');
COMMIT TRANSACTION;
--This row will be "stuck" in an open transaction, causing a dirty read
BEGIN TRANSACTION;
INSERT INTO dbo.demo(col1, col2)
    VALUES (42, 'Dirty read');
--Do not COMMIT TRANSACTION or ROLLBACK TRANSACTION here

Query 2 - Read the rows including the open transaction:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM dbo.demo;

Returns:

col1 col2

----------- ------------------

99 Normal transaction

42 Dirty read

P.S.: Don't forget to clean up this demo data:

COMMIT TRANSACTION;
DROP TABLE dbo.demo;
GO

SQL Server Try_Convert Function

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same.

Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

TRY_CONVERT() returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Data_type - The datatype into which to convert. Here length is an optional parameter which helps to get result in specified length.

Expression - The value to be convert

Style - style accepts the same values as the style parameter of the CONVERT function.

[ For more information, see CAST and CONVERT (Transact-SQL). ]

DECLARE @sampletext AS VARCHAR(10);
SET @sampletext = '123456';

DECLARE @realDate AS VARCHAR(10);
SET @realDate = '12/09/2015';

SELECT TRY_CONVERT(INT, @sampletext); -- 123456
SELECT TRY_CONVERT(DATETIME, @sampletext); -- NULL
SELECT TRY_CONVERT(DATETIME2, @realDate); -- 2015-12-09 00:00:00.0000000

Here is more example

SET DATEFORMAT dmy;  
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  --NULL
GO
SET DATEFORMAT mdy;  
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  --2010-12-31 00:00:00.0000000
GO