SSIS 2005 two digit year

Question

SSIS 2005 two digit year

p.carloo

SSC Enthusiast

Points: 136
More actions
August 15, 2011 at 6:57 am

#145962

Hello everyone,
I am encountering a problem with a SSIS package. In the package I have a source table and a destination table. In the source table there is a field called validity_date which is of type varchar. I perform a Data Conversion using DT_Date to convert this field to datetime. I insert the converted field in the destination table.
In the source table the field is stored in the format DD/MM/YY and the two digit cut off year for SQL server 2005 is 2050. The problem that I am encountering is that the date 23/12/50 in the source table gets inserted as '1950-12-23 00:00:00.000' instead of '2050-12-23 00:00:00.000' in the destination table. Since the two digit cut off year is 2050, the year 50 should be interpreted as 2050 instead of 1950.
Can anyone please provide me some feedback regarding this issue?
Thanks and kind regards.

Viewing 4 posts - 1 through 4 (of 4 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

The default time span for Microsoft SQL Server is 1950-2049, which represents a cutoff year of 2049. This means that SQL Server interprets a two-digit year of 49 as 2049, a two-digit year of 50 as 1950

Read this for how the cut off year can be altered

http://msdn.microsoft.com/en-us/library/ms191004(v=SQL.90).aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

p.carloo SSC Enthusiast Points: 136 More actions · Answer 2

Thanks for your reply Ron.

I can't change the two digit cut off year as I need the year 50 to be interpreted as 2050. If I put the cut off year to the default one (2049), the year 50 will be interpreted as 1950.

Also, when I perform an insert via SQL Server 2005, the year 23/12/50 gets correctly inserted in the table as '2050-12-23 00:00:00' but when I execute it via the SSIS package, it gets inserted as '1950-12-23 00:00:00'.

I still don't understand where the problem lies.

Thanks.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

Try putting the cutoff year at 60 (NOT 49! That's going in the wrong direction!). That'll solve your immediate problem.

You're longer term problem is, of course, the 2 digit years. I strongly recommend fixing that particular problem which may require a 3rd party to change their methods a bit. Even if they're "sole source" for the data, many will be happy to make such changes because it's simply the right thing to do. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)