<div>
The SQL Server answer is below, which you'll have to edit to fit your situation and every column in question. &nbsp;Don't know the Oracle translation to this. <br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42351498-1"><span>SELECT DateColumn</span>
<span>FROM YourTable</span>
<span>WHERE ISDATE(DateColumn) = 0 AND DateColumn IS NOT NULL</span>
</code></pre>
</div>


<div>
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:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42351502-1"><span>Declare @test as Table (chardate varchar(10) null)</span>
<span></span>
<span>Insert into @test values ('5/1/1987'),(null),('13/13/1987'),('2/28/1987'),('28/2/1987');</span>
<span></span>
<span>Set dateformat mdy;</span>
<span>Select chardate, isdate(chardate) as &quot;isdate?&quot;, Try_convert(datetime, chardate) as converted from @test</span>
<span></span>
<span>Set dateformat dmy;</span>
<span>Select chardate, isdate(chardate) as &quot;isdate?&quot;, Try_convert(datetime, chardate) as converted  from @test</span>
</code></pre>
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.<br />
<br />
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?<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42351502-2"><span>-- find other examples than NULL</span>
<span>Select chardate, isdate(chardate) WHERE NOT chardate IS NULL;</span>
<span>-- find char dates missing slashes or having surplus slashes</span>
<span>Select chardate, isdate(chardate) WHERE DATALENGHT(chardate)-DATALENGTH(REPLACE(chardate,'/','')&lt;&gt;2; </span>
<span>--find char dates starting with 4 digits</span>
<span>Select chardate, isdate(chardate) WHERE LEFT(chardate,4) LIKE '[1-9][0-9][0-9][0-9]';</span>
</code></pre>
<br />
And try this with both dmy and mdy dateformat.<br />
And last not least how did you convert?<br />
<br />
Bye, Olaf.
</div>


<div>
Maybe you have the actual string 'NULL' in the data?<br />
<br />
CAST(NULLIF(column_name, 'NULL') AS datetime)<br />
<br />
Also, re-check for <b>small</b>datetime in the table (rather than datetime), which could cause an overflow for certain date values.
</div>


<div>
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).
</div>


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


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


<div>
PortletPaul, &nbsp;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. <br />
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.
</div>


<div>
Kumar, Try_Cast I believe is only available 2012 and above. But I used the below code to see where it was breaking:<br />
<br />
Select * from MyTable<br />
&nbsp;Where cast(DATE_STARTED as datetime) is not null<br />
&nbsp;order by id<br />
<br />
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.
</div>


<div>
<pre><code class="code-1 nolinks" id="code-20-42353106-1"><span>Select * from MyTable</span>
<span> Where cast(DATE_STARTED as datetime) is not null</span>
<span> order by id</span>
</code></pre>
<br />
Wouldn't that just give you all rows where the CAST works?<br />
<br />
To find, where it NOT works, shouldn't you query this:<br />

<pre><code class="code-1 nolinks" id="code-20-42353106-2"><span>Select * from MyTable</span>
<span> Where cast(DATE_STARTED as datetime) is null</span>
<span> order by id</span>
</code></pre>
<br />
Was that the problem all along, perhaps?<br />
<br />
Sorry, I didn't check 2008 R2 doesn't have TRY_CONVERT.<br />
<br />
Bye, Olaf.
</div>


<div>
<div class="content wysiwyg-content">
I was provided data (believe from Oracle Server) that has several date columns. Some of the columns imported to datetime no issue while others are varchar. I need to switch the varchar to datetime. I can't determine what records are the issue. I've tried the isdate but the only records it brings back are null. I took of the time and AM hoping that was the issue but didn't work. There are over 600,000 records so how can I determine the issue? Here are a few record examples:<br />
12/1/1986<br />
1/1/1987<br />
2/1/1987<br />
4/1/1987<br />
5/1/1987<br />
<br />
Using SQL Server 2008 R2
</div>
</div>


I was provided data (believe from Oracle Server) that has several date columns. Some of the columns imported to datetime no issue while others are varchar. I need to switch the varchar to datetime. I can't determine what records are the issue. I've tried the isdate but the only records it brings back are null. I took of the time and AM hoping that was the issue but didn't work. There are over 600,000 records so how can I determine the issue? Here are a few record examples:
12/1/1986
1/1/1987
2/1/1987
4/1/1987
5/1/1987

Using SQL Server 2008 R2

Convert varchar to datetime returns out-of-range value

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.