Varchar to date

Kothapalli Srinivasarao 131

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

Viorel 118K Reputation points

2022-10-13T10:03:06.703+00:00

Which varchar value did not work in this case: CONVERT(date, dt, 103)? Show an example. Normally it should work.
Kothapalli Srinivasarao 131 Reputation points

2022-10-13T10:34:32.033+00:00

10/08/2020
10/08/2020
21/10/2020
20/10/2020

these are examples of values,
table contain 263000 reacords
Kothapalli Srinivasarao 131 Reputation points

2022-10-13T10:35:38.087+00:00

i can not use functions

Viorel 118K

All of the shown examples seem to works. Try on your machine in Management Studio:

select CONVERT(date, '10/08/2020', 103)  
select CONVERT(date, '10/08/2020', 103)  
select CONVERT(date, '21/10/2020', 103)  
select CONVERT(date, '20/10/2020', 103)

Maybe there are other values that are invalid. To investigate the problematic rows, try a query:

select * from MyTable   
where dt is not null and TRY_CONVERT(date, dt, 103) is null

Accepted answer

Olaf Helper 45,101 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
Please sign in to rate this answer.

1 person found this answer helpful.
Kothapalli Srinivasarao 131 Reputation points

2022-10-13T10:45:13.787+00:00

when I am executing the above query, getting two records in the result set.

Dt
03/17/2021
03/17/2021

Kothapalli Srinivasarao 131 Reputation points

2022-10-13T10:48:12.077+00:00

Thank you
I find out the invalid values
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Olaf Helper 45,101 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.
Please sign in to rate this answer.
Kothapalli Srinivasarao 131 Reputation points

2022-10-13T10:41:43.35+00:00

No Use
, i can not use functions, i Need to convert "dt" column values(263000) to DateTime,without using declare function

Olaf Helper 45,101 Reputation points

2022-10-13T10:47:41.177+00:00

without using declare function

TRY_CONVERT is a normal build-in SQL function.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Varchar to date

1 additional answer

Your answer