SQL Server Business Intelligence

SQL SERVER 2005 & 2008 IS THE MAIN THEME OF THIS BLOG

Thursday, April 27, 2006

2 Digit Date Format in SSIS

If I run the following SQL into SQL Server via SSMS it will load "31-DEC-49" as 2049-12-31.
INSERT
INTO Stg_StockProductCostPrice
VALUES (0,9999,0.99,'15-NOV-75','31-DEC-49')


But if I have the same data in a file and load it via SSIS it is loading into the database as 1949-12-31, instead of 2049-12-31.

There is a property called "Two Digit Year Cutoff" against the server. It is defaulted to 2049.

SSIS uses the Windows settings. The work around is to use derived column and get what you wanted as shown below:

(DT_DBTIMESTAMP) (SUBSTRING(CostPriceEndDate, 1, 7) + ((DT_I2)SUBSTRING(CostPriceEndDate, 8, 2) > 10 ? "19" : "20") + SUBSTRING(CostPriceEndDate, 8, 2))

**** PLEASE READ BELOW IF YOU HAVE SQL 2005 SP1 ****

http://wiki.sqlis.com/default.aspx/SQLISWiki/DateConversions.html
http://blogs.conchango.com/jamiethomson/archive/2006/04/26/3870.aspx

Thanks
Sutha

0 Comments:

Post a Comment

<< Home