In the Suzuki method of teaching violin, there is a saying that, “Japanese children speak Japanese.” It means that although Japanese is a difficult language to learn for those brought up with another language, it's natural and not particularly difficult for Japanese children who are surrounded by it all the time. We're all brought up with the concepts of date and time and we don't think of them as being difficult. When we program however, the complex concepts of date and time can become all too apparent.

When programming, there are four distinct ways to think of date and time data:

• As a date and a time: Occurs on a specific day, at a specific time, regardless of time zone. Each user of this information will see the exact same date and time, regardless of their local time zone. An example is a scheduled appointment.
• As a point in time: Occurs on a specific day, at a specific time, in a specific time zone. Each user of this information will convert it to their own local time zone. An example is when a contract is executed.
• As a date only: Occurs on a specific day, regardless of time zone. Each user of this information will see exactly the same date, regardless of their local time zone. An example is a birthday or a due date.
• As a time only: Occurs at a specific time, regardless of date or time zone. Each user of this information will see exactly the same time, regardless of their local date and time zone. An example is a recurring appointment time.

There are two more variations that aren't very common: Date at a point in time and time at a point in time. Each user of this information converts it to their own local time zone. As an example, an event that occurs on April 10 in Sydney Australia may be read in Denver USA as occurring on April 9 because Denver is almost a day behind Sydney.

Each of these scenarios must be handled differently, using different data types and coding strategies, both in .NET and in SQL Server. If your program and data are both located in a single time zone and you're sure that will never change, then you can take some shortcuts and you probably don't need to read this article. If, however, you write software that must work in more than one time zone and/or your program runs in a different time zone than your data (for instance, when part of the system is in the cloud), you should probably stay with me.

If you write software that must work in more than one time zone and/or your program runs in a different time zone than your data, stay with me.

How .NET and SQL Server Handle Dates and Times

.NET and SQL Server have always come up short when it comes to handling date and time data. To be fair, most other languages and databases do as well. Although there have always been ways to cover the inadequacies, the work-arounds have always felt clumsy to me. We deal with them so often in our daily lives to the point that they seem rather simple and intuitive to us, yet dates and times are complicated concepts. Initially, both .NET and SQL Server set out to handle some of the most complicated aspects for us, each with its own DateTime data type.

.NET's DateTime could handle various calendars, such as the Hebrew calendar, and both .NET and SQL Server could handle leap years (including special leap years like 2000), formatting (for example mm/dd/yy vs. dd/mm/yyyy), addition and subtraction of the various components of date and time such as year, month, day, hour (12 or 24-hour format), minutes, seconds, and milliseconds, calculations for differences in dates, times, and datetimes, etc. Yet there are deficiencies. Take, for example, anyone who works with very old dates, such as a historian who wants to enter dates BC, as there is almost no support for eras. And of course, the range of values that can be stored in .NET data types vs. SQL Server data types is all over the place. For example, in .NET a DateTime object can store dates as early as 01/01/0001, the SQL Server DateTime type can only store dates from 01/01/1753, and the SQL Server SmallDateTime type can only store dates from 01/01/1900 through 06/06/2079. Yes, I would need a VERY good reason to use SQL Server's SmallDateTime data type. Also, .NET DateTimes can store fractions of a second to seven decimal places, and the SQL Server DateTime type can only store fewer than three decimal places of precision.

I've run into most of these issues on one project or another over the years, although each was a specific case outside the realm of the typical business application. For example, I worked on an application that stored historical information about towns and cities in the United States. Thank goodness it wasn't Europe, Africa, or Asia, where we'd need support for BC dates! Even so, my town has Native American pueblo that date to around 1000 AD, and the current town was settled by the Spanish in the 1500s and was officially established as a Spanish town in 1615. The SQL Server DateTime data type didn't (and still doesn't) support these dates.

SQL Server 2008 made a big leap forward when they introduced us to the DateTime2 and DateTimeOffset data types. DateTime2 brought SQL Server on par with the range and precision of the .NET DateTime object that would have solved my issue with historical dates for U.S. towns and cities once my ORM began supporting them. DateTimeOffset could record a very precise point in time that is globally understood because it includes time zone information. As luck would have it, .NET Framework 3.5 also introduced a DateTimeOffset type around the same time as SQL Server 2008.

Time zones are complicated. You'd expect there to be 24 time zones in the world, but currently, there are 38. Some have an offset of 30 minutes, and others 45 minutes, and time zones do occasionally change. DateTimeOffsets also includes support for daylight savings time, another complex topic linked to time zones. The United States and its territories for example, spans a total of 11 or 12 “standard” time zones, depending on who you ask, though by law, there are only officially nine. The contiguous U.S. has four “standard” time zones and four “daylight savings” time zones for a total of eight. However, Arizona doesn't use daylight savings time, except for the Navajo Nation in Arizona, which does. Simple, right?

Many of you have noticed that support for time zones and daylight savings time were actually available in .NET Framework prior to version 3.5. In fact, there has always been some support for them in the DateTime data type since the very beginning in .NET Framework 1.1. Well, yes and no. I've always considered time zone support in the DateTime type to be a mistake. A mistake that has cost developers untold thousands of hours of headaches, and here's why. Prior to 3.5, the .NET Framework didn't have a TimeZoneInfo type, so DateTimes didn't support time zones at all. When 3.5 came along, they did.

Much worse is the fact that the DateTime type stored time zone information some of the time, but not all of the time. It has a Kind property whose value is either Local, UTC, or Unspecified and this value has to be checked and accounted for every time you read or write a DateTime if you want it to behave consistently. For example, when a value is set with DateTime.Now, it will have its Kind property set to Local and a value set with new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day) will have its Kind property set to Unknown. If you build n-tiered applications, DateTime.Now gives you local time on a client computer and, (more than likely) UTC time in the cloud (although in the cloud, its Kind property is still set to Local with a time zone offset of 0). And, when persisted to SQL Server, the time zone information was completely lost, unless you created a separate column to hold the offset and handled that yourself.

For me, living in the Central U.S. time zone at the time, this mess first manifested itself in a new application where, late in the day, transactions started showing up as happening the next day. Sometimes. At the time, we were on Central Standard time, which meant we were UTC-6, so after 6 P.M., things were being stamped with times just after midnight because we were running our services in the cloud where the clocks were set to the UTC time zone. But only some things showed up this way, others showed up as expected. That all boiled down to how we created and read the DateTime values in .NET. You see, DateTimes with Kind = Unspecified were being recorded as expected, but those with Kind = Local were being adjusted to UTC, and then serialized, sent 3,000 miles, deserialized, and stored in SQL Server where the time zone information isn't supported (unless it's done manually). Our solution to the problem was to sub-class the DateTime object and write code to ensure that the Kind property was always set to Unknown, no matter how the DateTime object was created or modified and to ensure that all of our DateTimes were created in the client, in the local time zone, and never in the services. Of course, we had to go through all of our code and make sure we always used the new sub-class. It was a hack. All of our testing up to that point was on local hardware, so the issue didn't appear until dangerously close to the go-live date for the project, so we had to scramble. Others solve the problem by always converting DateTime values to UTC before storing in the database.

The new DateTimeOffset data type handles most of these issues IF you want to store a point in time value for a location. This is for things like storing the exact moment a contract is executed. You store DateTimeOffset.Now and all of the information, including time zone information, is stored to the SQL Server DateTimeOffset column. But it's not so great for storing things like appointments. If you make an appointment in New Jersey for 3 P.M. on March 12 and an assistant in New Mexico pulls it up, it will appear to be at 1 P.M. because 1 P.M. in New Mexico is the same as 3 P.M. in New Jersey. Our hack to fix this was the sub-class of DateTime mentioned above.

TimeOnly and DateOnly data types are now available to match the Date and Time data types in SQL Server.

And, of course, sometimes you only want to store the date and don't want the time. Birthdays are a great example, or due dates. Why have we been forced to save these in DateTime objects and then ensure that the time portion is set to midnight, and further to make sure the Kind property is set to Unspecified so that the date doesn't accidentally change when persisted? Ugly! Sure, DateTime has a few things to help us, like the Today property that returns today's date at midnight this morning, but all of the ugliness persists. And if you only want to store a time, such as a recurring appointment, every Wednesday at 3 P.M.?

SQL Server 2014 introduced the Date and Time data types. Date handles dates back to 01/01/0001 and Time handles ranges from 00:00:00 through 23:59:59 with precision for seconds to seven decimal places. Unfortunately, .NET didn't immediately support similar data types.

SQL Server Date and Time Data Types

Table 1 includes the date- and time-related data types available in SQL Server 2014 and later (from SQL Server 2019 docs online https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15).

.NET 6 Date and Time Data Types

Table 2 shows some of the rich date- and time-related information available to us in .NET 6 and later.

.NET 6 introduced two of my favorite advances in .NET in a long time. TimeOnly and DateOnly data types are now available to match the Date and Time data types in SQL Server. This solves these problems once and for all. No more hacks using DateTime and writing code around them to handle the oddities. Figure 1 shows some date and time data types.

Figure 1: .NET 6 date and time data types

In Conclusion

In this article, you've seen how SQL Server, the .NET Framework, and now .NET have evolved in their handling of dates and times. You looked at some of the issues with date and time data and how we've worked around them until support for new features came about. You saw the evolution from just the DateTime type in both .NET and SQL Server to the DateTime2 type in SQL Server 2008, which solved some of the inconsistencies between .NET and SQL Server, and which improves the first way programmers handle dates and times. You saw new DateTimeOffset types introduced in .NET Framework 3.5 and SQL Server 2008, which helped solve the second way programmers need to handle dates and times: as a point in time. And you saw the new Date and Time types introduced in SQL Server 2014, solving the third and fourth ways programmers handle dates and times. Finally, you took a look at the new DateOnly and TimeOnly types introduced in .NET 6 that finally give us a first-class way of using the new Date and Time types in SQL Server in .NET code.

There are still more improvements needed to “finish” date and time handling, from my point of view. .NET needs to introduce its own DateTime2 data type to match the DateTime2 class in SQL Server. It shouldn't even attempt to handle time zone information in any way, shape, or form so we can finally drop all the weirdness introduced in the original DateTime class. SQL Server needs to add a TimeSpan type to match the one in .NET and perhaps native support for specifying calendars and time zone information. With .NET 6, we're one big step closer to being able to handle all the subtleties of date- and time-related data in all the different ways we, as programmers, need. Perhaps one day we'll get SQL Server support for eras. And it's never too early to start thinking about how we'll track dates and times on Mars, where the days are 37 minutes and 22 seconds longer than they are here on Earth. Then again, I suppose we have a little more time before we have to worry about that!