I have a .dat file that is being loaded in SSIS into an SQL table. There are multiple fields that are specifically designated to be "datetime." The source data file is for a COBOL system. The COBOL system has "00/00/00" automatically entered into several of these fields so an end user can see the field is for a date. Many of these date fields never have anything entered and remain "00/00/00." In the .dat file, the "00/00/00" is interpreted to be a single zero. Then, when the SSIS package tries to insert a zero into a date field, SQL interprets that zero to be "2000-01-01 00:00:00.000." How can I use a derived column to make the single 0 a null before SQL interprets it to be 2000-01-01? I've combed the web and cannot find a solution to this. I know it is something like...REPLACE(db_catty_to_date) == 0 ISNULL ? do_nothing_to_field_and_accept_a_valid_date_that_it_contains. Can ANYONE give me a solution to this? MANY THANKS!!!

Hi RJHead, Just replace the value with NULL(DT_DBTIMESTAMP) in that case (with a Derived Column transformation). Like this: [YourDateColumn] == (DT_DBTIMESTAMP)"2000-01-01 00:00:00.000" ? NULL(DT_DBTIMESTAMP) : [YourDateColumn] Regards, Zoli

ZOLTAN, YOU ARE THE ONE!!! THANK YOU! MERCI! GRAZIE! DANKE! GRACIAS!

Um, did it save the day? :) In that case, send me a chocolate bar... er, no, wait... just hit "Mark as Answer", that will be OK ;)

What I am most concerned about is whether or not it is leaving a legitimate date of 1/1/2000 alone. If an end user enters 1/1/2000 as a legitimate date, then it should not be changed. I've got to do this for several other date fields in the same table. So far, I've not known of 1/1/2000 being entered as a legitimate date for that particular field. Your solution is changing 1/1/2000 to NULL. I was of the opinion that some CAST or CONVERT provision would have to be used to allow SSIS to 'see' a single zero in a date field before that zero translated to 1/1/2000. In other words, convert the date field to INT so the zero could be read, read to see if there was actually a zero, and then act to change a 0 to a NULL. Your statement says (as best as I can tell) "See if there is a 1/1/2000 and change it to NULL, otherwise leave it alone." Your solution has not evaluated a zero, but rather a specific date (which might be just fine), and acted appropriately on that specific date. THANKS! R. J. HeadR. J. Head