Convert varchar to datetime returns out-of-range value

MadIce
MadIce used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Olaf DoschkeSoftware Developer

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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).

Author

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

Author

Commented:
OK, tried everything mentioned. Most have tried before except the smalldatetime. Only thing left is the Month Day swap. will try next.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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

Author

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.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
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

Author

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.
Olaf DoschkeSoftware Developer

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial