Working with Dates

Question

Working with Dates

Paul_Harvey

Hall of Fame

Points: 3822
More actions
January 19, 2010 at 4:49 am

#209240

My application (and other componenets that hang off it) cannot handle the default date of 1899-12-30 00:00:00.000 that SSIS places when a date is null.
I am trying to find a way, either through the Script Component (preferrred) or a derived Column to set the field to NULL if it is equal to 1899-12-30 00:00:00.000.
Can anyone help with this please?
I have tried:
[DOB] == '1899-12-30 00:00:00.000' ? NULL : [DOB] and varius other permutations. I'd rather do it in the Script component, but any which way will do!
Thanks,
Paul

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

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

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 1

I have now added a Execute SQL Task component after the data has been written to a table, where the unwanted dates are set to NULL.

Would still love to know if this could have been done without the need for an additional component, ie within the Script Component that is creating the value in the first place.

LutzM SSC Guru Points: 107049 More actions · Answer 2

Would the following work (untested)?

CASE WHEN [DOB] = '1899-12-30 00:00:00.000' THEN NULL ELSE [DOB] END

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 3

I use a case just like Lutz suggested; only difference is depending on what the data is capturing, I have a cut off date, so i don't accept dates that are over 100 years old for example:

CASE WHEN [DOB] < DATEADD(yy, -100,getdate()) THEN NULL ELSE [DOB] END

for me, i often have to check the validity of things like shipping dates, posted funding dates, etc, where tey could not have occurred prior tot he company existing and things like that.

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!

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 4

Thanks for the advice.

The syntax that is in use here.. is it intended to be used is the Script Component, Derived Column or T-SQL? It doesn't quite work for any, returning various errors.

I have managed to remove the dates in T_SQL.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I have dealt with a similar situation and here is the SSIS Expression that I use in a Derived Column transform:

DateCol == (DT_DATE)"12/30/1899" ? NULL(DT_DATE) : DateCol

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 6

Paul_Harvey

Hall of Fame

Points: 3822

January 19, 2010 at 7:25 am

#1105404

Brilliant Jack...

That did it!!

Thanks a lot.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Glad I could help. The key in SSIS Expressions is to remember that everything has to be typed correctly including NULL.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question