The Two-Digit Cutoff For Years

Claudio Silva explains why a two-digit year may be interpreted differently in SQL Server versus Excel:

What do you read when you see some date in a format like “01-Jan-00 00:00:00.000”? Keep in mind that I’m talking about the output directly from the table and without any formatting.
1st of January seems to leave no doubt (just because there is no default date format starting with two digits for the year), but…what about the year part ’00’?
It stands for 1900 and the 3rd column is wrong?
Or it stands for 2000 and the DATEPART function is returning the wrong value?

This is why you want to stick with four-digit years.  But if you’re stuck with two-digit years for some reason, Claudio explains how you can get Excel and SQL Server to return the same results.

Related Posts

Extended Event Duration Units of Measure

Emanuele Meazzo shows how we can find out whether that duration column is milliseconds or microseconds: Even if I use Extended Events almost every day, I always forget the unit of measure of each duration counter, since they’re basically arbitrary; Seconds, milliseconds, microseconds? Whatever, it depends on the dev that implemented that specific counter. That’s […]

Read More

Dates in Base R

Michael Toth explains some of the functionality available in base R (that is, not packages like lubridate) for working with dates: When working with R date formats, you’re generally going to be trying to accomplish one of two different but related goals: 1. Converting a character string like “Jan 30 1989” to a Date type […]

Read More


September 2018
« Aug Oct »