Varchar to date

Question

Hi,

I am converting the varchar value to date.
the date values are stored in the SQL table in the format of DD/MM/YYYY.

below query was tried to convert varchar to date :
CONVERT(date, dt, 103)

Error :
Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string.

and,
when I try the below query:
CONVERT(datetime,CONVERT(VARCHAR,dt ,103),103)
Error is;
Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

query:
CONVERT(date,CONVERT(VARCHAR,dt ,103),103)

error:

Msg 241, Level 16, State 1, Line 6
Conversion failed when converting date and/or time from character string.

-- 'dt' is column name
pls help me to resolve it

Answer 1

That example values are convertable. You can analyse your extisting 263 K records on not-convertable by using TRY_CONVERT function

SELECT *  
FROM yourTable  
WHERE TRY_CONVERT(date, columnField, 103) IS NULL  

Answer 2

datetime data type resulted in an out-of-range value.

datetime has a valid range from 1753-01-01 til 9999-12-31. If the date is before 1753, you get this error, see
https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16

Please post some sample values you try to convert.