Tom Tom - 1 year ago 105
SQL Question

SQL Date change upon CSV import

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?

Answer Source

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

Hope this helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download