Weird situation during csv file import via SSIS to a sql table.
CSV date 25-May-46 imported into 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 csv column to a date within excel, I get '1946-05-25'
Would anyone have an idea what may be causing this?
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.