Post reply

add value 0 to a datetime column in sql with SSIS

  • September 15, 2010 at 8:57 am

    #227784

    Hi,

    I have a table in SQL Servwer 2005 where one of my columns is a datetime (needs to be that way). I have a SSIS process where it loads data from a Flat file into that table.

    In the Flat File, the date column can be like 2010-09-14 14:00:01 (which is loaded without problem into the database).. or it can be 0 (zero).

    I get this error when I try to insert the 0

    There was an error with input column "luaDate" (8895) on input "OLE DB Destination Input" (6477). The column status returned was: "The value could not be converted because of a potential loss of data.".

    I havent found the correct option to make the value 0 be inserted into my datetime column in sql. the data should be value 0 in Sql (1900-01-01 00:00:00.000).

    Any idea?

    Thank you in advance

  • September 15, 2010 at 10:26 am

    #1221508

    I found the answer. It was easier that I thought.

    in a Derived Column use this:

    REPLACE(luaDate,"0","1900-01-01")

    THAT'S ALL... lol

    good luck!

  • September 15, 2010 at 10:47 am

    #1221525

    Since this is a character field, what happens to a date that just happens to contain a zero (like year 2010, or days 10, 20, 30)? You might need to do an IIF(luaDate="0", "1900/01/01", luaDate) instead.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 15, 2010 at 12:09 pm

    #1221583

    you are absolutly right!!

    I think the right expression for a Derived Column is:

    luaDate == "0" ? (dt_dbtimestamp)"1900/01/01" : (dt_dbtimestamp)luaDate

    but Im getting this error:

    The conditional operation failed.

    SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Replace 0 for DateValue" (11831)" failed because error code 0xC0049063 occurred, and the error row disposition on "input column "luaDate" (12075)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

    I tried without the dateStamp and with the String and nothing.. What Im missing ?

  • September 15, 2010 at 1:30 pm

    #1221633

    Wouldn't you use DT_DBDATE instead of DT_DBTIMESTAMP? Also, try replacing the "/" with "."

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 15, 2010 at 1:34 pm

    #1221635

    I try them as well.. and nothing... :crying:

  • September 16, 2010 at 6:34 am

    #1222125

    Try using "1900-01-01".

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 22, 2010 at 9:46 am

    #1225713

    I'm assuming that your [luaDate] column is a string type (DT_STR or DT_WSTR). The error you're getting implies that it's not.

    An expression like this should work... Make sure you're creating a new column and not trying to place this expression back into the [luaDate] column. (That could cause you trouble too.)

    And yes - use dashes in dates. "1900-01-01" instead of "1900/01/01".

    Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
    My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components

  • September 22, 2010 at 9:49 am

    #1225715

    Thank you

    it works exactly like that. It was an error in the original Flat Files.. we re processes them and everything works fine.

    Regards,

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

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