2

Is there any SQL function that I can use to return all those records that have last 2 numbers greater than 31. Basically, there is one column: OpenDate in INT datatype stored as '19810281' and the last 2 values are 81(which should be a date) so it has erroneous data.

I want to query all those bad records which have last 2 numbers of column: OpenDate as greater than 31? Any suggestion would be greatly appreciated.

Geetanjali Sachdeva
  • 133
  • 1
  • 5
  • 14

1 Answers1

5

You can use the Modulo function.

SELECT OpenDate 
FROM Table
WHERE OpenDate % 100 > 31

As you are on 2008 you can use the DATE datatype. This is one byte smaller than an int and will prevent this type of issue.

Or alternatively a more robust check would be to cast the value to char and use isdate as yyyymmdd is unambiguous.

SELECT OpenDate
FROM   TABLE
WHERE  LEN(OpenDate) <> 8
        OR ISDATE(CAST(OpenDate AS CHAR(8))) = 0
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    I guess the check should be more complex, because February could have 28 or 29 days. Maybe some check including casting the value to a date. It is a pity that we cannot use `TRY_CONVERT`. – gotqn Mar 23 '15 at 19:31
  • @gotqn - True I just answered the question as asked. Will extend. – Martin Smith Mar 23 '15 at 19:32