Search code examples
sql-servercsvssisssis-2012

SQL Date change upon CSV import

Weird situation during .csv file import via SSIS into a SQL Server table.

CSV date 25-May-46 is imported into a NVARCHAR column.

When selecting that column on 

REPLACE(CONVERT(NVARCHAR(10), CAST(date_of_birth AS DATE), 111), '/', '-')

I get '2046-05-25'

When converting that column to a date in Excel, I get '1946-05-25'

Does anyone have an idea what may be causing this?

Solution

  • You should check the "Two Digit Year Cutoff" parameter at the SSMS - rightclick on your SQL Server - Server Properties - Advanced Settings. Default value is 2049, and you have expected result for this setting. Set it to 2045, to get '1946-05-25' on SQL Server.

    Also you can check/set this value like that:

    EXEC sys.sp_configure N'two digit year cutoff' --to check

EXEC sys.sp_configure N'two digit year cutoff', N'2045' --to set
GO
RECONFIGURE WITH OVERRIDE
GO

    Hope this helps.