1 //------------------------------------------------------------------------------
2 // <copyright file="SQLDateTime.cs" company="Microsoft">
3 // Copyright (c) Microsoft Corporation. All rights reserved.
5 // <owner current="true" primary="true">junfang</owner>
6 // <owner current="true" primary="false">Microsoft</owner>
7 // <owner current="true" primary="false">Microsoft</owner>
8 //------------------------------------------------------------------------------
10 //**************************************************************************
11 // @File: SqlDateTime.cs
15 // Purpose: Implementation of SqlDateTime which is equivalent to
16 // data type "datetime" in SQL Server
22 // 09/17/99 JunFang Created and implemented as first drop.
25 //**************************************************************************
28 using System.Data.Common;
29 using System.Diagnostics;
30 using System.Runtime.InteropServices;
31 using System.Globalization;
33 using System.Xml.Schema;
34 using System.Xml.Serialization;
36 namespace System.Data.SqlTypes {
37 using System.Threading;
41 /// Represents the date and time data ranging in value
42 /// from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds
43 /// to be stored in or retrieved from a database.
48 [StructLayout(LayoutKind.Sequential)]
49 [XmlSchemaProvider("GetXsdType")]
50 public struct SqlDateTime : INullable, IComparable, IXmlSerializable {
52 private bool m_fNotNull; // false if null
53 private int m_day; // Day from 1900/1/1, could be negative. Range: Jan 1 1753 - Dec 31 9999.
54 private int m_time; // Time in the day in term of ticks
58 // Number of (100ns) ticks per time unit
59 private const double SQLTicksPerMillisecond = 0.3;
60 public static readonly int SQLTicksPerSecond = 300;
61 public static readonly int SQLTicksPerMinute = SQLTicksPerSecond * 60;
62 public static readonly int SQLTicksPerHour = SQLTicksPerMinute * 60;
63 private static readonly int SQLTicksPerDay = SQLTicksPerHour * 24;
65 private const long TicksPerSecond = TimeSpan.TicksPerMillisecond * 1000;
67 private static readonly DateTime SQLBaseDate = new DateTime(1900, 1, 1);
68 private static readonly long SQLBaseDateTicks = SQLBaseDate.Ticks;
70 private const int MinYear = 1753; // Jan 1 1753
71 private const int MaxYear = 9999; // Dec 31 9999
73 private const int MinDay = -53690; // Jan 1 1753
74 private const int MaxDay = 2958463; // Dec 31 9999 is this many days from Jan 1 1900
75 private const int MinTime = 0; // 00:00:0:000PM
76 private static readonly int MaxTime = SQLTicksPerDay - 1; // = 25919999, 11:59:59:997PM
78 private const int DayBase = 693595; // Jan 1 1900 is this many days from Jan 1 0001
81 private static readonly int[] DaysToMonth365 = new int[] {
82 0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365};
83 private static readonly int[] DaysToMonth366 = new int[] {
84 0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 366};
86 private static readonly DateTime MinDateTime = new DateTime(1753, 1, 1);
87 private static readonly DateTime MaxDateTime = DateTime.MaxValue;
88 private static readonly TimeSpan MinTimeSpan = MinDateTime.Subtract(SQLBaseDate);
89 private static readonly TimeSpan MaxTimeSpan = MaxDateTime.Subtract(SQLBaseDate);
90 private const String x_ISO8601_DateTimeFormat = "yyyy-MM-ddTHH:mm:ss.fff";
92 // These formats are valid styles in SQL Server (style 9, 12, 13, 14)
93 // but couldn't be recognized by the default parse. Needs to call
94 // ParseExact in addition to recognize them.
95 private static readonly String[] x_DateTimeFormats = {
96 "MMM d yyyy hh:mm:ss:ffftt",
97 "MMM d yyyy hh:mm:ss:fff",
98 "d MMM yyyy hh:mm:ss:ffftt",
99 "d MMM yyyy hh:mm:ss:fff",
105 private const DateTimeStyles x_DateTimeStyle = DateTimeStyles.AllowWhiteSpaces;
108 private SqlDateTime(bool fNull) {
114 public SqlDateTime(DateTime value) {
115 this = FromDateTime(value);
118 public SqlDateTime(int year, int month, int day)
119 : this(year, month, day, 0, 0, 0, 0.0) {
122 public SqlDateTime(int year, int month, int day, int hour, int minute, int second)
123 : this(year, month, day, hour, minute, second, 0.0) {
126 public SqlDateTime(int year, int month, int day, int hour, int minute, int second, double millisecond) {
127 if (year >= MinYear && year <= MaxYear && month >= 1 && month <= 12)
129 int[] days = IsLeapYear(year)? DaysToMonth366: DaysToMonth365;
130 if (day >= 1 && day <= days[month] - days[month - 1])
133 int dayticks = y * 365 + y / 4 - y / 100 + y / 400 + days[month - 1] + day - 1;
136 if (dayticks >= MinDay && dayticks <= MaxDay &&
137 hour >= 0 && hour < 24 && minute >= 0 && minute < 60 &&
138 second >=0 && second < 60 && millisecond >= 0 && millisecond < 1000.0)
140 double ticksForMilisecond = millisecond * SQLTicksPerMillisecond + 0.5;
141 int timeticks = hour * SQLTicksPerHour + minute * SQLTicksPerMinute + second * SQLTicksPerSecond +
142 (int)ticksForMilisecond;
144 if (timeticks > MaxTime)
146 // Only rounding up could cause time to become greater than MaxTime.
147 SQLDebug.Check(timeticks == MaxTime + 1);
149 // Make time to be zero, and increment day.
154 // Success. Call ctor here which will again check dayticks and timeticks are within range.
155 // All other cases will throw exception below.
156 this = new SqlDateTime(dayticks, timeticks);
162 throw new SqlTypeException(SQLResource.InvalidDateTimeMessage);
165 // constructor that take DBTIMESTAMP data members
166 // Note: bilisecond is same as 'fraction' in DBTIMESTAMP
167 public SqlDateTime(int year, int month, int day, int hour, int minute, int second, int bilisecond)
168 : this (year, month, day, hour, minute, second, (double)bilisecond / 1000.0) {
172 public SqlDateTime(int dayTicks, int timeTicks) {
173 if (dayTicks < MinDay || dayTicks > MaxDay || timeTicks < MinTime || timeTicks > MaxTime) {
175 throw new OverflowException(SQLResource.DateTimeOverflowMessage);
183 internal SqlDateTime(double dblVal) {
184 if ((dblVal < MinDay) || (dblVal >= MaxDay + 1))
185 throw new OverflowException(SQLResource.DateTimeOverflowMessage);
187 int day = (int) dblVal;
188 int time = (int)((dblVal - day) * SQLTicksPerDay);
190 // Check if we need to borrow a day from the day portion.
193 time += SQLTicksPerDay;
195 else if (time >= SQLTicksPerDay) {
196 // Deal with case where time portion = 24 hrs.
198 // ISSUE: Is this code reachable? For this code to be reached there
199 // must be a value for dblVal such that:
200 // dblVal - (long)dblVal = 1.0
201 // This seems odd, but there was a bug (51261) that resulted because
202 // there was a negative value for dblVal such that dblVal + 1.0 = 1.0
205 time -= SQLTicksPerDay;
208 this = new SqlDateTime(day, time);
214 get { return !m_fNotNull;}
218 private static TimeSpan ToTimeSpan(SqlDateTime value) {
219 long millisecond = (long)(value.m_time / SQLTicksPerMillisecond + 0.5);
220 return new TimeSpan(value.m_day * TimeSpan.TicksPerDay +
221 millisecond * TimeSpan.TicksPerMillisecond);
224 private static DateTime ToDateTime(SqlDateTime value) {
225 return SQLBaseDate.Add(ToTimeSpan(value));
228 // Used by SqlBuffer in SqlClient.
229 internal static DateTime ToDateTime(int daypart, int timepart) {
230 if (daypart < MinDay || daypart > MaxDay || timepart < MinTime || timepart > MaxTime) {
231 throw new OverflowException(SQLResource.DateTimeOverflowMessage);
233 long dayticks = daypart * TimeSpan.TicksPerDay;
234 long timeticks = ((long)(timepart / SQLTicksPerMillisecond + 0.5)) * TimeSpan.TicksPerMillisecond; //
236 DateTime result = new DateTime(SQLBaseDateTicks + dayticks + timeticks);
240 // Convert from TimeSpan, rounded to one three-hundredth second, due to loss of precision
241 private static SqlDateTime FromTimeSpan(TimeSpan value) {
242 if (value < MinTimeSpan || value > MaxTimeSpan)
243 throw new SqlTypeException(SQLResource.DateTimeOverflowMessage);
245 int day = value.Days;
247 long ticks = value.Ticks - day * TimeSpan.TicksPerDay;
251 ticks += TimeSpan.TicksPerDay;
254 int time = (int)((double)ticks / TimeSpan.TicksPerMillisecond * SQLTicksPerMillisecond + 0.5);
255 if (time > MaxTime) {
256 // Only rounding up could cause time to become greater than MaxTime.
257 SQLDebug.Check(time == MaxTime + 1);
259 // Make time to be zero, and increment day.
264 return new SqlDateTime(day, time);
267 private static SqlDateTime FromDateTime(DateTime value) {
268 // DevNote: SqlDateTime has smaller precision and range than DateTime.
269 // Usually we round the DateTime value to the nearest SqlDateTime value.
270 // but for DateTime.MaxValue, if we round it up, it will overflow.
271 // Although the overflow would be the correct behavior, we simply
272 // returned SqlDateTime.MaxValue in v1. In order not to break exisiting
273 // code, we'll keep this logic.
275 if (value == DateTime.MaxValue)
276 return SqlDateTime.MaxValue;
277 return FromTimeSpan(value.Subtract(SQLBaseDate));
281 internal static SqlDateTime FromDouble(double dblVal) {
282 return new SqlDateTime(dblVal);
285 internal static double ToDouble(SqlDateTime x) {
286 AssertValidSqlDateTime(x);
287 return(double)x.m_day + ((double)x.m_time / (double)SQLTicksPerDay);
290 internal static int ToInt(SqlDateTime x) {
291 AssertValidSqlDateTime(x);
292 return x.m_time >= MaxTime / 2 ? x.m_day + 1 : x.m_day;
297 // do we still want to define a property of DateTime? If the user uses it often, it is expensive
299 public DateTime Value {
302 return ToDateTime(this);
304 throw new SqlNullValueException();
308 // Day ticks -- returns number of days since 1/1/1900
309 public int DayTicks {
314 throw new SqlNullValueException();
318 // Time ticks -- return daily time in unit of 1/300 second
319 public int TimeTicks {
324 throw new SqlNullValueException();
328 // Implicit conversion from DateTime to SqlDateTime
329 public static implicit operator SqlDateTime(DateTime value) {
330 return new SqlDateTime(value);
333 // Explicit conversion from SqlDateTime to int. Returns 0 if x is Null.
334 public static explicit operator DateTime(SqlDateTime x) {
335 return ToDateTime(x);
338 // Return string representation of SqlDateTime
339 public override String ToString() {
341 return SQLResource.NullString;
342 DateTime dateTime = ToDateTime(this);
343 return dateTime.ToString((IFormatProvider)null);
346 public static SqlDateTime Parse(String s) {
349 if (s == SQLResource.NullString)
350 return SqlDateTime.Null;
353 dt = DateTime.Parse(s, CultureInfo.InvariantCulture);
355 catch (FormatException) {
356 DateTimeFormatInfo dtfi = (DateTimeFormatInfo)(Thread.CurrentThread.CurrentCulture.GetFormat(typeof(DateTimeFormatInfo)));
357 dt = DateTime.ParseExact(s, x_DateTimeFormats, dtfi, x_DateTimeStyle);
360 return new SqlDateTime(dt);
366 // Arithmetic operators
368 // Alternative method: SqlDateTime.Add
369 public static SqlDateTime operator +(SqlDateTime x, TimeSpan t) {
370 return x.IsNull ? Null : FromDateTime(ToDateTime(x) + t);
373 // Alternative method: SqlDateTime.Subtract
374 public static SqlDateTime operator -(SqlDateTime x, TimeSpan t) {
375 return x.IsNull ? Null : FromDateTime(ToDateTime(x) - t);
378 //--------------------------------------------------
379 // Alternative methods for overloaded operators
380 //--------------------------------------------------
382 // Alternative method for operator +
383 public static SqlDateTime Add(SqlDateTime x, TimeSpan t) {
387 // Alternative method for operator -
388 public static SqlDateTime Subtract(SqlDateTime x, TimeSpan t) {
394 // Implicit conversions
396 // Implicit conversion from SqlBoolean to SqlDateTime
397 public static implicit operator SqlDateTime(SqlBoolean x)
399 return x.IsNull ? Null : new SqlDateTime(x.Value, 0);
402 // Implicit conversion from SqlInt32 to SqlDateTime
403 public static implicit operator SqlDateTime(SqlInt32 x)
405 return x.IsNull ? Null : new SqlDateTime(x.Value, 0);
408 // Implicit conversion from SqlMoney to SqlDateTime
409 public static implicit operator SqlDateTime(SqlMoney x)
411 return x.IsNull ? Null : SqlDateTime.FromDouble(x.ToDouble());
415 // Explicit conversions
417 // Explicit conversion from SqlDateTime to SqlInt32
418 public static explicit operator SqlInt32(SqlDateTime x)
421 return SqlInt32.Null;
423 return new SqlInt32(SqlDateTime.ToInt(x));
426 // Explicit conversion from SqlDateTime to SqlBoolean
427 public static explicit operator SqlBoolean(SqlDateTime x)
430 return SqlBoolean.Null;
432 return new SqlBoolean(x.m_day != 0 || x.m_time != 0, false);
435 // Explicit conversion from SqlDateTime to SqlMoney
436 public static explicit operator SqlMoney(SqlDateTime x)
438 return x.IsNull ? SqlMoney.Null : new SqlMoney(SqlDateTime.ToDouble(x));
441 // Implicit conversion from SqlDouble to SqlDateTime
442 public static implicit operator SqlDateTime(SqlDouble x)
444 return x.IsNull ? Null : new SqlDateTime(x.Value);
447 // Explicit conversion from SqlDateTime to SqlDouble
448 public static explicit operator SqlDouble(SqlDateTime x)
450 return x.IsNull ? SqlDouble.Null : new SqlDouble(SqlDateTime.ToDouble(x));
454 // Implicit conversion from SqlDecimal to SqlDateTime
455 public static implicit operator SqlDateTime(SqlDecimal x)
457 return x.IsNull ? SqlDateTime.Null : new SqlDateTime(SqlDecimal.ToDouble(x));
460 // Explicit conversion from SqlDateTime to SqlDecimal
461 public static explicit operator SqlDecimal(SqlDateTime x)
463 return x.IsNull ? SqlDecimal.Null : new SqlDecimal(SqlDateTime.ToDouble(x));
468 // Explicit conversion from SqlString to SqlDateTime
469 // Throws FormatException or OverflowException if necessary.
470 public static explicit operator SqlDateTime(SqlString x) {
471 return x.IsNull ? SqlDateTime.Null : SqlDateTime.Parse(x.Value);
481 private static void AssertValidSqlDateTime(SqlDateTime x) {
482 SQLDebug.Check(!x.IsNull, "!x.IsNull", "Datetime: Null");
483 SQLDebug.Check(x.m_day >= MinDay && x.m_day <= MaxDay, "day >= MinDay && day <= MaxDay",
484 "DateTime: Day out of range");
485 SQLDebug.Check(x.m_time >= MinTime && x.m_time <= MaxTime, "time >= MinTime && time <= MaxTime",
486 "DateTime: Time out of range");
490 // Checks whether a given year is a leap year. This method returns true if
491 // "year" is a leap year, or false if not.
493 // @param year The year to check.
494 // @return true if "year" is a leap year, false otherwise.
496 private static bool IsLeapYear(int year) {
497 return year % 4 == 0 && (year % 100 != 0 || year % 400 == 0);
500 // Overloading comparison operators
501 public static SqlBoolean operator==(SqlDateTime x, SqlDateTime y) {
502 return(x.IsNull || y.IsNull) ? SqlBoolean.Null : new SqlBoolean(x.m_day == y.m_day && x.m_time == y.m_time);
505 public static SqlBoolean operator!=(SqlDateTime x, SqlDateTime y) {
509 public static SqlBoolean operator<(SqlDateTime x, SqlDateTime y) {
510 return(x.IsNull || y.IsNull) ? SqlBoolean.Null :
511 new SqlBoolean(x.m_day < y.m_day || (x.m_day == y.m_day && x.m_time < y.m_time));
514 public static SqlBoolean operator>(SqlDateTime x, SqlDateTime y) {
515 return(x.IsNull || y.IsNull) ? SqlBoolean.Null :
516 new SqlBoolean(x.m_day > y.m_day || (x.m_day == y.m_day && x.m_time > y.m_time));
519 public static SqlBoolean operator<=(SqlDateTime x, SqlDateTime y) {
520 return(x.IsNull || y.IsNull) ? SqlBoolean.Null :
521 new SqlBoolean(x.m_day < y.m_day || (x.m_day == y.m_day && x.m_time <= y.m_time));
524 public static SqlBoolean operator>=(SqlDateTime x, SqlDateTime y) {
525 return(x.IsNull || y.IsNull) ? SqlBoolean.Null :
526 new SqlBoolean(x.m_day > y.m_day || (x.m_day == y.m_day && x.m_time >= y.m_time));
529 //--------------------------------------------------
530 // Alternative methods for overloaded operators
531 //--------------------------------------------------
533 // Alternative method for operator ==
534 public static SqlBoolean Equals(SqlDateTime x, SqlDateTime y) {
538 // Alternative method for operator !=
539 public static SqlBoolean NotEquals(SqlDateTime x, SqlDateTime y) {
543 // Alternative method for operator <
544 public static SqlBoolean LessThan(SqlDateTime x, SqlDateTime y) {
548 // Alternative method for operator >
549 public static SqlBoolean GreaterThan(SqlDateTime x, SqlDateTime y) {
553 // Alternative method for operator <=
554 public static SqlBoolean LessThanOrEqual(SqlDateTime x, SqlDateTime y) {
558 // Alternative method for operator >=
559 public static SqlBoolean GreaterThanOrEqual(SqlDateTime x, SqlDateTime y) {
563 // Alternative method for conversions.
564 public SqlString ToSqlString() {
565 return (SqlString)this;
570 // Compares this object to another object, returning an integer that
571 // indicates the relationship.
572 // Returns a value less than zero if this < object, zero if this = object,
573 // or a value greater than zero if this > object.
574 // null is considered to be less than any instance.
575 // If object is not of same type, this method throws an ArgumentException.
576 public int CompareTo(Object value) {
577 if (value is SqlDateTime) {
578 SqlDateTime i = (SqlDateTime)value;
582 throw ADP.WrongType(value.GetType(), typeof(SqlDateTime));
585 public int CompareTo(SqlDateTime value) {
586 // If both Null, consider them equal.
587 // Otherwise, Null is less than anything.
589 return value.IsNull ? 0 : -1;
590 else if (value.IsNull)
593 if (this < value) return -1;
594 if (this > value) return 1;
598 // Compares this instance with a specified object
599 public override bool Equals(Object value) {
600 if (!(value is SqlDateTime)) {
604 SqlDateTime i = (SqlDateTime)value;
606 if (i.IsNull || IsNull)
607 return (i.IsNull && IsNull);
609 return (this == i).Value;
612 // For hashing purpose
613 public override int GetHashCode() {
614 return IsNull ? 0 : Value.GetHashCode();
618 /// <para>[To be supplied.]</para>
620 XmlSchema IXmlSerializable.GetSchema() { return null; }
623 /// <para>[To be supplied.]</para>
625 void IXmlSerializable.ReadXml(XmlReader reader) {
626 string isNull = reader.GetAttribute("nil", XmlSchema.InstanceNamespace);
627 if (isNull != null && XmlConvert.ToBoolean(isNull)) {
628 // VSTFDevDiv# 479603 - SqlTypes read null value infinitely and never read the next value. Fix - Read the next value.
629 reader.ReadElementString();
633 DateTime dt = XmlConvert.ToDateTime(reader.ReadElementString(), XmlDateTimeSerializationMode.RoundtripKind);
634 // We do not support any kind of timezone information that is
635 // possibly included in the CLR DateTime, since SQL Server
636 // does not support TZ info. If any was specified, error out.
638 if (dt.Kind != System.DateTimeKind.Unspecified)
640 throw new SqlTypeException(SQLResource.TimeZoneSpecifiedMessage);
643 SqlDateTime st = FromDateTime(dt);
645 m_time = st.TimeTicks;
651 /// <para>[To be supplied.]</para>
653 void IXmlSerializable.WriteXml(XmlWriter writer) {
655 writer.WriteAttributeString("xsi", "nil", XmlSchema.InstanceNamespace, "true");
658 writer.WriteString(XmlConvert.ToString(Value, x_ISO8601_DateTimeFormat));
663 /// <para>[To be supplied.]</para>
665 public static XmlQualifiedName GetXsdType(XmlSchemaSet schemaSet) {
666 return new XmlQualifiedName("dateTime", XmlSchema.Namespace);
669 public static readonly SqlDateTime MinValue = new SqlDateTime(MinDay, 0);
670 public static readonly SqlDateTime MaxValue = new SqlDateTime(MaxDay, MaxTime);
672 public static readonly SqlDateTime Null = new SqlDateTime(true);
676 } // namespace System.Data.SqlTypes