2.3.14 SQL DateTime and SmallDateTime

SQL DateTime and SmallDateTime are used to store date and time information originating from the database date and time values.

  DayTicks = number of days since 1900-1-1
  DateTicks = signed 4 byte little-endian integer with value of DayTicks
  SmallDateTicks = unsigned 2 byte little-endian integer with value of DayTicks
  SQLTicksPerMillisecond = 0.3
  SQLTicksPerSecond = 300
  SQLTicksPerMinute = SQLTicksPerSecond * 60
  SQLTicksPerHour = SQLTicksPerMinute * 60
  TicksForMilliseconds = round-off(Milliseconds *
                          SQLTicksPerMillisecond + 0.5)
                         ; Round-off means disregard decimal points,
                         ; so 1.9 is turned into 1
  TotalTimeTicks = Hours * SQLTicksPerHour +
                     Minutes * SQLTicksPerMinute +
                       Seconds * SQLTicksPerSecond +
                         TicksForMilliseconds
  TimeTicks = unsigned 4 byte little-endian integer with value of TotalTimeTicks
                         ; This is the number of seconds times 300
  SmallTotalTimeTicks = Hours * 60 + Minutes
  SmallTimeTicks = unsigned 2 byte little-endian integer with value of SmallTotalTimeTicks
                         ; This is the number of minutes
  DateTime = DateTicks TimeTicks
  SmallDateTime = SmallDateTicks SmallTimeTicks

• Hours MUST range from 0 to 23.

• Milliseconds MUST range from 0 to 999.

• Minutes MUST range from 0 to 59.

• Seconds MUST range from 0 to 59.

Note that for TimeTicks, there are cases in which two different inputs are stored as the same value due to roundoff. For example, time 00:59:59.999 and time 01:00:00.000 are both stored as value 1080000. A parser SHOULD<18> round up during the parsing of such values and thus report the time of value 1080000 as 01:00:00.000.

The DateTime is used by the SQL-DATETIME atomic type.

The SmallDateTime is used by the SQL-SMALLDATETIME atomic type.