Server Option to allow/disallow 24:00 as a valid value for Time datatype - by lloydmalvern

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 776818 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 1/17/2013 10:20:25 AM
Access Restriction Public


Please add a server option that users can set to On or OFF, which if it is ON, will allow 24:00 as a valid Time.  Per the discussion and sample use case given here:

there is now no way to represent the terminus of the 24-hour clock unambiguously . The Time datatype is supposed to be based on the 24-hour day, but we can only represent the beginning of the 24-hour day with 00:00.  We cannot represent the end-point.

The Time datatype has nothing to do with calendrical time; as far as the Time datatype is concerned,  time consists of an eternal 24-hour day. There is no concept of yesterday or tomorrow in the Time datatype.  When mapping data with Time, we should not be concerned with tomorrow or yesterday.  If we are concerned with tomorrow or yesterday, or a particular day in the calendar, we use DateTime.

So a retort that "24:00" belongs to the NEXT day is not a valid objection as the retort is conflating DateTime with Time. To repeat, there is no concept of NEXT DAY as far as the Time datatype is concerned.  24:00 is NOT "tomorrow".  It is the terminus of TODAY.
Sign in to post a comment.
Posted by lloydmalvern on 1/18/2013 at 1:03 PM
Thank you, Umachandar. I appreciate your willingness to keep this issue open. I understand the legacy demands are not to be dismissed lightly, but my radically simple solution to the legacy problem is that developers should not expect the existing DateTime and SmallDateTime datatypes and the date-math functions to work with the Time datatype in the first place! The clock-time expressed by a Time datatype has nothing to do with any particular chronological/calender date. It was unfortunately named. Had it been called DayTime or ClockTime it might be seen as the very simple thing it is, a representation of the hours, minutes, seconds, and fractional seconds in a fictional, abstract, disconnected-from-the-calendar, 24-hour clock whose time runs from 00:00 to 24:00. It would be nice to have some new ClockTime math functions to go with it however, that could calculate the number of minutes, say, between 02:18 and 14:53.
Posted by Microsoft on 1/18/2013 at 12:37 PM
The ANSI/ISO SQL standards that we follow specifies TIME as a continuum in the 24 hr period. So it basically starts at 00:00:00.000 and ends at 23:59:59.999 (a 24-hr clock representation). The ISO 8601 standard which is different from the ANSI SQL standard actually allows using 24:00:00.000 as valid time value.

Unfortunately, SQL Server has a legacy in terms of the older datetime types (smalldatetime, datetime) and allows conversions to/from different datetime types. Allowing 24:00:00.000 only for time data type will complicate those conversions and optimizations that we do today. Perhaps we can revisit this design decision in the future if we get lot of requests. Anyway, I reopened your request to see what other customers think about this and access the feedback. Thanks again for providing your feedback.

Umachandar, SQL Programmability Team
Posted by lloydmalvern on 1/18/2013 at 9:17 AM
Actually, I want to to store a Time of Day using the Time datatype, not a datetime with DateTime and not an interval with TimeSpan.

If you see a value of 00:00 in a Time column, you cannot know whether it refers to "midnight this morning" or "midnight tonight" as there is not a different way to express those two different moments in the 24-hour day, the beginning of the day and the ending of the day. The Time datatype's day should begin at 00:00 and end at 24:00.

I believe the ANSI standard relates not to Time of Day but to the time chunk of the DateTime datatype. The Time datatype's concept of day is not the same thing as the DateTime datatype's concept of time. Whereas DateTime relates to calendrical time involving the sequence of days going forwards into the future and backwards into history, the Time datatype is a finite set that is bounded by 00:00 and 24:00. Yet in the current flawed implementation, there is no way to express 24:00. With DateTime, 2 JANUARY 2013 00:00 can unambiguously represent the terminus of 1 JANUARY 2013. With Time, we have no date chunk to increment, and 00:00 is ambiguous.
Posted by Microsoft on 1/17/2013 at 3:07 PM
Hello Lloyd,
Thanks for your feedback. Our implementation of the new datetime types follows the ANSI SQL specification. Datetime types represent date or time values (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE) only. So as such for time values the valid values for HOUR are 00 to 23 and MINUTE are 00 to 59. I don't see us changing the current implementation of the TIME data type like you requested. Additionally it will also complicate indexing/storage in the server. As for server / database options, we typically avoid adding more fo those to affect language behavior since it is not a user friendly option.
However, based on the description of your problem it seems like you want to store intervals (units of date or time). ANSI SQL has a type called INTERVAL that is suited for this purpose. We are already tracking that request below and hope to add it to a future version of SQL Server.

If you would like to know more about INTERVAL type then please refer to the ANSI SQL specification or Oracle documentation.

Umachandar, SQL Programmability Team