Convert varchar to datetime returns out-of-range value

The SQL Server answer is below, which you'll have to edit to fit your situation and every column in question.  Don't know the Oracle translation to this.

SELECT DateColumn
FROM YourTable
WHERE ISDATE(DateColumn) = 0 AND DateColumn IS NOT NULL

Select allOpen in new window

Those dates you give as example surely will never be problematic, but of course isdate() of invalid dates will become 0, not only for NULL. It will depend on dateformat dmy or mdy:

Declare @test as Table (chardate varchar(10) null)

Insert into @test values ('5/1/1987'),(null),('13/13/1987'),('2/28/1987'),('28/2/1987');

Set dateformat mdy;
Select chardate, isdate(chardate) as "isdate?", Try_convert(datetime, chardate) as converted from @test

Set dateformat dmy;
Select chardate, isdate(chardate) as "isdate?", Try_convert(datetime, chardate) as converted  from @test

Select allOpen in new window

This has ('2/28/1987') 1 in the first result and ('28/2/1987') 1 in the second result and vice versa. And notice 5/1 converts to either 5th January or 1st May depending on dateformat.

When you only detected nulls, did you try to find out more examples by a) playing with dateformat and b) filtering out all null dates to see other examples?

-- find other examples than NULL
Select chardate, isdate(chardate) WHERE NOT chardate IS NULL;
-- find char dates missing slashes or having surplus slashes
Select chardate, isdate(chardate) WHERE DATALENGHT(chardate)-DATALENGTH(REPLACE(chardate,'/','')<>2; 
--find char dates starting with 4 digits
Select chardate, isdate(chardate) WHERE LEFT(chardate,4) LIKE '[1-9][0-9][0-9][0-9]';

Select allOpen in new window

And try this with both dmy and mdy dateformat.
And last not least how did you convert?

Bye, Olaf.

Maybe you have the actual string 'NULL' in the data?

CAST(NULLIF(column_name, 'NULL') AS datetime)

Also, re-check for smalldatetime in the table (rather than datetime), which could cause an overflow for certain date values.

Check if the day and month weren't swapped. Is the only thing that I can think now that might return the out-of-range error (for example, month 13 does not exist).

I have checked with ISDATE prior but this time added the is not null. No records returned. review other mentioned suggestions

OK, tried everything mentioned. Most have tried before except the smalldatetime. Only thing left is the Month Day swap. will try next.

PortletPaul,  script works great. But came back with no errors. So can't understand why I can't go into design view and change to datetime or smalldatetime. Also, can't update another table with the data.
What I think I'll try next is use this code to rebuild the date from the three separate columns. I'll try that in the morning.

Kumar, Try_Cast I believe is only available 2012 and above. But I used the below code to see where it was breaking:

Select * from MyTable
 Where cast(DATE_STARTED as datetime) is not null
 order by id

I looked at the next record in order where it error and saw 1010 as the year. I should of saw this in PortletPaul code. Have to check again to see why it didn't. I will use PortletPaul but the above code as an alternative.

Select * from MyTable
 Where cast(DATE_STARTED as datetime) is not null
 order by id

Select allOpen in new window

Wouldn't that just give you all rows where the CAST works?

To find, where it NOT works, shouldn't you query this:

Select * from MyTable
 Where cast(DATE_STARTED as datetime) is null
 order by id

Select allOpen in new window

Was that the problem all along, perhaps?

Sorry, I didn't check 2008 R2 doesn't have TRY_CONVERT.

Bye, Olaf.