Skip to content
Pascal Arnould By Pascal Arnould Software Engineer III
Azure Table Storage throws a StorageException when using DateTime.Min

1601 was the year that Pierre de Fermat and King Louis XIII of France were born, Robert Devereux was beheaded in the Tower of London, the Dutch defeated the Portuguese navy in the battle of Bantam Bay and the year Shakespear's Hamlet is thought to have been performed for the first time. Monday January, 1st 1601 (in the Gregorian calendar) was a pretty uneventful day yet the first day of the 17th century is affecting millions today. Why? Because Windows uses it as the base date to calculate timestamps and the current date by counting the number of ticks (aka jiffy) that have lapsed since that epoch date. In other words, in Windows, a date is represented by the number of 100 nanosecond intervals since 1601-01-01 (ISO 8601) and stored as a 64-bit integer value. All time values can then be converted to the more commonly used calendar dates, taking into account timezones, daylight saving (DST) and leap seconds. Interestingly the reference date corresponding to time zero and the tick value used vary between operating systems and languages.

Platforms - Languages Tick Epoch date
Windows, NTFS, COBOL 100 ns January 1, 1601
MS DOS 1 s January 1, 1980
.NET 100 ns January 1, 1
SQL Server 1/300 ms January 1, 1753
Mac OS (up to version 9) 1 s January 1, 1904
Mac OS X, Unix, Linux, C, Android, Java, JavaScript 1 s January 1, 1970

s = second / ms = millisecond / ns = nanosecond

Side note on the Gregorian Calendar

The Gregorian calendar which was first adopted by Italy, Poland, Portugal and Spain, was introduced by Pope Gregory XIII (1502-1585) in 1582 to replace the Julian Calendar (named after the Roman Emperor Julius Caesar in 45BC). Britain only adopted it in 1752.

Azure Weekly is a summary of the week's top Microsoft Azure news from AI to Availability Zones. Keep on top of all the latest Azure developments!

A regular Gregorian year consists of 365 days while a leap year which occurs every 4 years has 366 days with an intercalation or leap day added as February 29. One Gregorian calendar cycle consists of:

  • 400 years
  • 303 regular years
  • 97 leap years (with 3 leap days omitted)
  • 20,871 seven-day weeks
  • 146,097 days

The length of each month can be calculated as follows:

L = 30 + ((M + floor(^(M)/₈)) MOD 2) - (M == 2 ? F : 0)

L = month length in days

M = month number 1 to 12

The best hour you can spend to refine your own data strategy and leverage the latest capabilities on Azure to accelerate your road map.

F = leap year ? 1 : 2 (Adjustment for February)

A leap year must satisfy the following rule:

  1. The year is evenly divisible by 4
  2. If the year can be evenly divided by 100, it is not a leap year, unless
  3. The year is also evenly divisible by 400. Then it is a leap year.

Why 1601-01-01?

This date was first used by the American National Standards Institute (ANSI) for COBOL programming and by Windows subsequently. The rational behind it was that it made some datetime based calculations easier. As noted above the Gregorian calendar leap years occur every 4 years and years that are divisible by 100 are not leap years except when they are also divisible by 400. So when Windows was designed, 1600 was the century leap year available at the time (year 2000 being next and 2400 after that) and January 1, 1601 was therefore the first day of a new 400-year Gregorian calendar cycle. All dates and times in Windows have subsequently been calculated based on the number of 100 ns that have elapsed since hour 0 of that day.

How is this relevant to Azure table storage?

You might have guessed it by now. Azure table storage uses 1601-01-01 as its epoch date. I was first made aware of it when trying to save an entity with a System.DateTime property set to the default .NET value (i.e. 01-01-01) and a Microsoft.WindowsAzure.Storage.StorageException was thrown. I've therefore added a simple check to ensure that the application I'm working on doesn't attempt to save any 'invalid' dates:

Note that null values are valid in this context.

Found any inaccuracies in this post? Please leave a comment.

Pascal Arnould on Twitter

Sign up** to Azure Weekly to receive Azure related news and articles direct to your inbox or follow on Twitter: **@azureweekly

Pascal Arnould

Software Engineer III

Pascal Arnould

He has over 20 years experience of of implementing complex technology solutions across a number of sectors, and is a passionate advocate of Agile practices, continuous learning and engineering excellence.

Pascal worked at endjin from 2013 - 2015.