Varchar to date

Kothapalli Srinivasarao 131 Reputation points
2022-10-13T09:49:15.63+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,869 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

Accepted answer
  1. Olaf Helper 41,021 Reputation points
    2022-10-13T10:38:34.927+00:00

    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  
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,021 Reputation points
    2022-10-13T10:33:04.417+00:00

    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.