Convert varchar to datetime returns out-of-range value

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
MadIceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Open in new window

0
Olaf DoschkeSoftware DeveloperCommented:
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

Open 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]';

Open in new window


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

Bye, Olaf.
0
Scott PletcherSenior DBACommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
0
MadIceAuthor Commented:
I have checked with ISDATE prior but this time added the is not null. No records returned. review other mentioned suggestions
0
MadIceAuthor Commented:
OK, tried everything mentioned. Most have tried before except the smalldatetime. Only thing left is the Month Day swap. will try next.
0
PortletPaulEE Topic AdvisorCommented:
Perhaps something like this will help identify the bad rows?
    CREATE TABLE Table1
        ([datecol] varchar(9))
    ;
      
    INSERT INTO Table1
        ([datecol])
    VALUES
        ('12/1/1986'),
        ('1/1/1987'),
        ('2/1/1987'),
        ('4/1/1987'),
        ('5/1/1987'),
        ('17/1/2010'),
        ('1/17/2011'),
        ('rubbish')
    ;

Open in new window

    select
            t.datecol, ca2.m, ca2.d, ca2.y
          , isdate(ca2.m + '/' + ca2.d + '/' +  ca2.y) mdy
          , isdate(ca2.d + '/' + ca2.d + '/' +  ca2.y) dmy
    from table1 t
    cross apply (
        select
               charindex ( '/' , t.datecol+'/' ,1 )    x
             , charindex ( '/' , reverse(t.datecol)+'/' ,1 ) y
             , len(datecol) ldate
        ) ca1
    cross apply (
        select
               case when ldate-x-y > 0 then right(t.datecol, y-1) end y
             , case when ldate-x-y > 0 then left(t.datecol, x-1) end m
             , case when ldate-x-y > 0 then substring(t.datecol,x+1,len(t.datecol)-y-x) end d
        ) ca2
    where isdate(ca2.m + '/' + ca2.d + '/' +  ca2.y) = 0
       or isdate(ca2.d + '/' + ca2.d + '/' +  ca2.y) = 0

Open in new window

    |   datecol |      m |      d |      y | mdy | dmy |
    |-----------|--------|--------|--------|-----|-----|
    | 17/1/2010 |     17 |      1 |   2010 |   0 |   1 |
    | 1/17/2011 |      1 |     17 |   2011 |   1 |   0 |
    |   rubbish | (null) | (null) | (null) |   0 |   0 |

Open in new window

see  http://sqlfiddle.com/#!6/0bc26/1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MadIceAuthor Commented:
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.
0
Pawan KumarDatabase ExpertCommented:
Pls try this. This will give us the rows which has problems.

You need to run below in SSMS, query window.

Select * from yourtable
Where try_cast(datecol as datetime) is null
0
MadIceAuthor Commented:
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.
0
Olaf DoschkeSoftware DeveloperCommented:
Select * from MyTable
 Where cast(DATE_STARTED as datetime) is not null
 order by id

Open 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

Open in new window


Was that the problem all along, perhaps?

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

Bye, Olaf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.