How to find the wrong dates after an 'out-of-range value' error in SQL Server?-CodePudding

I want to convert a column in the format mm/dd/yyyy to datetime, but when I do I get the following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I found in other posts that this means that some dates don't make sense, such as 10/35/2021. I tried to find the wrong dates by slicing the varchars to get the dates with SUBSTRING(date, 3, 2) but it turns out some dates are in the form m/d/yyyy, so when I slice I get something like 1/.

I have no idea how to find the wrong dates, and how to (even though there are wrong dates) convert everything to datetime.

Thanks!

CodePudding user response：

If some of your data is in MM/dd/YYYY and some M/d/yyyy this really makes for a bit of a mess. I would likely do something like this:

--Add a new varchar column (yes varchar) to save a copy of your bad data
ALTER TABLE dbo.YourData ADD BadDate varchar(10);
GO
--Change the data you have to the ISO format yyyyMMdd and store the bad data in the BadDate column
UPDATE dbo.YourData
SET DateColumn = CONVERT(varchar(8),TRY_CONVERT(date,DateColumn,101),112),
    BadDate = CASE WHEN TRY_CONVERT(date,DateColumn,101) IS NULL THEN DateColumn END
WHERE DateColumn IS NOT NULL;
GO
--Change data type of your data column
ALTER TABLE dbo.YourData ALTER COLUMN DateColumn date NULL;
GO

--You can view your bad data with:
SELECT BadData
FROM dbo.YourData
WHERE BadData IS NOT NULL;

CodePudding user response：

If you just want to locate rows with values that are obviously bad dates - disregarding any ambiguities - just use try_convert and check for NULLs

with dates as (
 select * from (values('01/02/2021'),('02/01/2021'),('33/02/2021'),('01/13/2021'))v(d)
)
select *
from dates
where Try_Convert(date, d) is null