During the conversion of data from varchar to DateTime. we are getting the below error in procedure. The error was from data during conversion.

Query:
select cast('1752-10-05' as datetime);

Output:
Msg 242, Level 16, State 3, Line 201
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Cause: Data in table and cast function is out of range.

Solution: On checking on the internet found some valid points to validate the data in SQL Server:

1. Use year before than 1753, we will get the error as shown below the use cases:

select cast('1753-10-05' as datetime)
Output:
1753-10-05 00:00:00.000

select cast('1752-10-05' as datetime);

Output:
Msg 242, Level 16, State 3, Line 201
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

2. More use cases for error like the month of 30th data use 31st cause same error.

3. use of leap year setting of 29th Feb, cause the same error.

4. Date(1-31) or month(1-12) used out of range like 32 dates, 15 months cause the same error.

Track Wrong Date with help of Cursor & Exceptional Handling

If you want to track the wrong data, what exactly causing the problem then use the following cursor and exceptional handling method to trace the wrong data which causing the problem:

-- Create the error handling table:

CREATE TABLE Error_handling
(         UserName       VARCHAR(100),
          ErrorNumber    INT,
          ErrorState     INT,
          ErrorSeverity  INT,
          ErrorLine      INT,
          ErrorProcedure VARCHAR(500),
          ErrorMessage   VARCHAR(1000),
          ErrorDateTime  DATETIME,
	  ErrorData      VARCHAR(MAX)
 )

I have test table to show how we will trace the wrong data by help of cursor and exceptional handling:

create table test_Date(tracdate varchar(20));

insert into test_date values('2020-10-10');
insert into test_Date values('2011-10-10');
insert into test_Date values('2010-10-10');
insert into test_Date values('1700-10-10');  -- wrong data out of range
insert into test_Date values('1800-10-10');
insert into test_Date values('1600-10-10');  -- wrong data out of range

--Following cursor with help of exception handling will get the wrong data:
DECLARE Cn CURSOR for select tracdate from test_date;
Declare @founderrordata varchar(20);
Declare @founddate datetime;
open cn;
FETCH NEXT FROM CN into @founderrordata
while @@fetch_status = 0
Begin
BEGIN TRY
set @founddate = cast(@founderrordata as datetime);
END TRY
BEGIN CATCH
INSERT INTO dbo.error_handling
 VALUES (SUSER_SNAME(),ERROR_NUMBER(),ERROR_STATE(),
ERROR_SEVERITY(),ERROR_LINE(),ERROR_PROCEDURE(),ERROR_MESSAGE(),
GETDATE(), @founderrordata);
END CATCH
FETCH NEXT FROM CN into @founderrordata;
End
close cn;
deallocate cn;

-- After executing it, we get error in error_handling table which is getting wrong data, we can also put unique id or thing to identify.

select * from error_handling;