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?
 
PortletPaulfreelancerCommented:
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.