question

This calculation is correct based on the default two digit year cutoff server configuration option. Per [BOL][1], the span is 1950-2049 so 11/01/42 means 11/01/ 2042 while 11/01/50 means 11/01/1950. for example: select cast('11/01/42' as date) DOB; -- returns: DOB ---------- 2042-11-01 Thus, the difference (per datediff) in years between that date and today is -24. While it is possible to change the configuration for this specific option, doing so is ***ill advised*** and should be avoided at any cost. It would be far better to update the offensive values in the underlying table using some logic, like, for example, update the 2-digit year part to 4-digits based on the values (if last 2-digits <= 18 then prefix with 20 else prefix with 19). Here is the sample script: update YourTable set DOB = left(DOB, 6) + case when cast(right(DOB, 2) as int) <= 18 then '20' else '19' end + right(DOB, 2); The above is still not bullet proof because it might incorrectly update the records for people who are more than 100 years old, but it is still better than changing the configuration. Nevertheless, if you really do need to change the default value of 2049, you can use sp\_configure. First, run it as is (without any parameters) and observe whether you see the row with name value of **two digit year cutoff**. If you don't then you need to first enable **show advanced options** by running this: sp_configure 'show advanced options', 1; reconfigure with override; go Once this is done (or advanced options was already enabled), you can now view (by running the procedure without any parameters) or set the **two digit year cutoff** by running this script: exec sp_configure 'two digit year cutoff', 2029; -- or whatever is suitable reconfigure with override; go Again, I am not recommending this approach, just showing that it is possible though admittedly evil. Hope this helps. Oleg [1]: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option