Post reply

How to get the value NULL in place of 1900-01-17

  • December 19, 2011 at 8:59 am

    #248259

    Hello All,

    I am importing a file into a table. There is a date column in the file. I have defined as date datatype in the table for that column. If the value for the date is null in the file I am getting something else populated in table for that record as '1900-01-17'. I have used as

    CAST(NULLIF(DATEADD(D,16,CURRENT_STMT_DATE),' ') AS DATE)

    But it is not working. Please assist.

  • December 19, 2011 at 9:08 am

    #1423156

    '' or empty date = 1900-01-01

    dateadd 16 = 1900-01-17

    I'd try convert(date, nullif(str_datecol, ''))

  • December 19, 2011 at 9:31 am

    #1423170

    Thanks for the reply.

    Its not working.

    I have tried like this

    CONVERT(DATEADD(D,16,CURRENT_STMT_DATE),NULLIF([CURRENT_STMT_DATE],' '))

  • December 19, 2011 at 9:32 am

    #1423171

    Please post sample data and required output.

  • December 19, 2011 at 9:38 am

    #1423174

    Your Current_Stmt_Date is coming in as a BLANK not NULL. Your DateAdd is converting the BLANK to 1900-01-01 and then adding 16 days to it to get 1900-01-17. Then your NULLIF is comparing 1900-01-17 to BLANK ( converted to 1900-01-01 ), and outputting 1900-01-17, because they don't match.

    If you change the scope of the NULLIF, I think that it will work as expected.

    CAST(DATEADD(D,16,NULLIF(CURRENT_STMT_DATE,' ')) AS DATE)

    The other option is to leave the NULLIF where it is and compare to 1900-01-17 instead of 1900-01-01 (BLANK).

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 19, 2011 at 9:57 am

    #1423194

    Thanks Drew and Ninja. It worked.

    I used CAST(DATEADD(D,16,NULLIF(CURRENT_STMT_DATE,' ')) AS DATE)

    Thanks for your time.

Viewing 6 posts - 1 through 5 (of 5 total)

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