CODEWITHSUNDEEP
sql-servercsvssisssis-2012
Tom
Tom

Reputation: 5

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?

Upvotes: 0

Views: 350

Answers (2)

Pavel Botygin
Pavel Botygin

Reputation: 187

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.

Upvotes: 2

Greg Rebisz
Greg Rebisz

Reputation: 156

It could be a mismatch in the date format in the csv vs your machines localization. I reckon you should review the date format and check that it matches with you machines settings

Upvotes: 0

Related Questions