Post reply

Find invalid data in date column

  • February 1, 2011 at 9:02 am

    #231448

    I am using SQL server 2005.

    I a looking for a particular date in a column (date_added) that is entered incorrectly.

    Eg:- The date should be 20110130, but there might be something like 201101

    So any idea how to find this

  • February 1, 2011 at 9:16 am

    #1281003

    any idea please......

  • February 1, 2011 at 9:18 am

    #1281008

    ouch; looks like a varchar column is being used to hold dates, instead of a datetime datatype; i've inherited a few of those situations myself.

    one of the first things you should do is try to get the datatype changed, and start validating the data at data entry.

    anyway,

    I'd start off first by testing anything that is not in a date format, and then also the length of the field...anything that is not 8 characters.

    select * from mytable where isdate(date_add) =0

    select * from mytable where DATALENGTH(date_add) <> 8

    then i'd test the first four characters against expected valid values, say between 1994 and 2012

    select CONVERT(int,date_pt) from (

    SELECT

    left(date_add,4) as date_pt from mytable where DATALENGTH(date_add) >=4) myAlias

    WHERE CONVERT(int,date_pt) < 1994

    OR CONVERT(int,date_pt) > 2012

    then finally, i'd test the char pairs that would be in the string for month and day, and make sure they fall in the expected values (1-12 for month, 1-31 for day)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 1, 2011 at 10:06 am

    #1281041

    this returned me null columns. and not what i expect. any thing i am missing here

  • February 1, 2011 at 10:15 am

    #1281050

    not sure buddy; from what you described, the first two queries (i thought) would return something; can you show us the actual queries you tested with?

    what is the datatype of that date_added column?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 1, 2011 at 10:24 am

    #1281060

    It happened to me once in sql 2008.

    I wanted to change the data type of the column from date to datetime.

    When I did that in sql 2008, it failed and said the error that

    one of the values in hte column is out of range...the value was something 19000302 or watever.

    You can try to change the data type and see if it catches that value.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • February 1, 2011 at 10:40 am

    #1281073

    Please abstain from using Isdate function.It wont be of any help.For ex

    select Isdate(201101) Returns 1

    My best bet would be that first you check the length of the column and then check whether the column has only numeric values and then compare them with date ranges.Something like this

    yourcolumn between '19000101' and '99990101'

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • February 1, 2011 at 1:12 pm

    #1281118

    select Isdate(201101) Returns 1

    not sure this helps but

    select isdate(Stuff('201101', 4, 0, ' ')) returns 0

    http://www.sqlservercentral.com/scripts/T-SQL/72143/

  • February 1, 2011 at 1:35 pm

    #1281132

    You could create a work table with the column formatted as datetime. Then use SSIS to copy from your current table into the work table. You choose a setting in SSIS to output failed rows to another table. Then you will have all the bad records in a separate table and can go back & fix them in the live table.

    You don't have to jump through hoops identifying bad data. Let SQL do it for you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply