I have data in excel sheet that I am importing into sql server. data in excel sheet contains some dates in dd/mm/yy format. sql server allows me to import the date as it is and appends the century to make it complete four digit year.
yyyy-mm-dd but in some cases it has guessed it correct and some cases it has guessed it wrong.
for example I have date column with values like
sql server prefixed the correct century for first three values but for the last value it add in sql server as
why is this and how can i fix it. thank you for your help in advance :)
Best How To :
This is because of the
Two Digit Year CutOff Property on your server level setting.
If you goto your SSMS , right click on your server name --> Properties --> Advance tab .
You will see a value there I think by default it is set to 2049, which means if you pass a value like your format dd/mm/yy SQL Server will complete the year bit depending on the value of YY in your passed values where YY value is greater than 49 Sql Server assumes it is 19th Century
01/01/99 will become
on the other hand if the value of YY is less than 49 Sql Server assumes it is 20th century.
For exmaple in your case
03/03/2048 in your sql server.
You can decrease the range of cut off year like 2020 which means if there is a value less than
01/01/20 on then sql server will assume it is
01/01/2020 any thing greater then 20 in your
YY will be assumed as