SQL Server How to fast locate bad data in a select query using cast function? [closed]

fumotvh3 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(68)

Closed. This question needs debugging details . It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem . This will help others answer the question.

Closed 22 days ago.
The community is reviewing whether to reopen this question as of 19 days ago.
Improve this question

In my SQL Server database, there is a varchar column begintime which has value like 2023/07/10 in table t1.

I use

select cast(begintime as date) from t1

to convert it to date and it return error message said some data can't be converted. After checking all values in the table finally I found the reason. Some data 's length is 12 instead of 10,with a return and blank character in the end.I wonder is there some tools in sql server studio which can fast locate the bad data? Any help?
Thanks

sql-server

来源：https://stackoverflow.com/questions/76650634/how-to-fast-locate-bad-data-in-a-select-query-using-cast-function

1条答案

按热度按时间

esbemjvw1#

use CAST or CONVERT built in functions to make 2023/7/10 as 2023/07/10

select CAST('2023/7/10' as date);
select CONVERT(date ,'2023/7/10');

above line's gives out put if date is valid to convert else gives error response.

you may use TRY_CAST or TRY_CONVERT this will give you out put if date is valid to convert else return NULL

select TRY_CAST('2023/17/10' as date); -- returns NULL
select TRY_CAST('2023/7/10' as date); -- returns 2023-07-10
select TRY_CONVERT(date ,'2023/27/10'); -- returns NULL
select TRY_CONVERT(date ,'2023/7/10'); -- returns 2023-07-10

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server How to fast locate bad data in a select query using cast function? [closed]

1条答案

相关问题

热门标签

最新问答