2

The following code compiles fine with comparison operator.

If(dateTimeVariable > SqlDateTime.MinValue) //compiles Ok. dateTimeVariable is of type DateTime
{
}

However, the following code fails to compile. 

DateTime dateTimeVariable=SqlDateTime.MinValue;
//Throws exception , cannot convert source type SqlDateTime to DateTime. Which is obvious.

My question is why comparison is allowed between SqlDateTime and Datetime types but not assignment. (Unless comparison operators are doing some implicit conversion.)

I'm guessing I must be missing something really basic.

ANewGuyInTown
  • 5,957
  • 5
  • 33
  • 45
  • You are correct in your suspicion that comparison causes implicit conversion. See my answer below to understand why. – Peter Wone Jun 03 '15 at 03:24

2 Answers2

2

There's an implicit conversion in SqlDateTime that takes care of converting a DateTime to an SqlDateTime without any additional work:

public static implicit operator SqlDateTime(DateTime value)
{
    return new SqlDateTime(value);
}

// SqlDateTime mySqlDate = DateTime.Now

What must be happening is that dateTimeVariable is being implicitly converted from a DateTime to an SqlDateTime for the comparison:

if (dateTimeVariable > SqlDateTime.MinValue)
{
    // if dateTimeVariable, after conversion to an SqlDateTime, is greater than the
    //  SqlDateTime.MinValue, this code executes
}

But in the case of the following code, there's nothing that allows you to simply stuff an SqlDateTime into a DateTime variable, so it doesn't allow it.

DateTime dateTimeVariable = SqlDateTime.MinValue;  // fails

Cast your initial value and it will compile okay, but there's a chance you're going to lose some valuable information that is part of an SqlDateTime but not a DateTime.

DateTime dateTimeVariable = (DateTime)SqlDateTime.MinValue;
Grant Winney
  • 65,241
  • 13
  • 115
  • 165
  • Since I'm comparing `dateTimeVariable` to `SqlDateTime`, shouldn't it try to implicitly convert `SqlDateTime` to `DateTime` instead not the vice versa? (Since it's on the left side of the `>` operator, similar to the `=` operator which fails to do implicit conversion? – ANewGuyInTown Jun 04 '15 at 02:45
  • Yes, I was just going with the analogy. Since `DateTime` ">" `SqlDateTime` "comparison" succeeds (possibly due to implicit conversion(?). I guess with the position of `SqlDateTime` being on the `right`, it should have tried to convert `SqlDateTime` to `DateTime`), I assume `DateTime` "=" `SqlDateTime` "assignment" should succeed too.(well just in theory) – ANewGuyInTown Jun 04 '15 at 02:58
2

This is a question of potential loss of precision. Usually this occurs in the context of "narrowing" versus "widening".

Integers are a subset of numbers. All integers are numbers, some numbers are not integers. Thus, the type "number" is wider than the type "integer".

You can always assign a type to a wider type without losing information.

Narrowing is another matter. To assign 1.3 to an integer you must lose information. This is possible but the compiler won't perform a narrowing conversion unless you explicitly state that this is what you want.

As a result, assignments that require a widening conversion are automatically and implicitly converted, but narrowing assignments require explicit casting or conversion (not all conversions are simple casting).

Although arguably SqlDateTime is narrower than DateTime differences in representation mean that conversions in both directions are potentially lossy. As a result, to assign a SqlDateTime to a DateTime requires an explicit conversion. Conversion of DateTime to SqlDateTime strictly speaking ought to require explicit conversion but the implicit conversion implemented in the SqlDateTime type (qv Grant's answer) makes SqlDateTime behave as though it were wider. I made the mistake of assuming SqlDateTime was wider because that's how it's behaving in this case and many kudos to commenters for picking out this important subtlety.

This implicit conversion thing is actually a bit of an issue with VARCHAR columns and ADO.NET implicitly typed parameters, because C# strings are Unicode and become NVARCHAR, so comparing them to an indexed column of type VARCHAR will cause a widening conversion to NVARCHAR (the implicit widening conversions thing also occurs in TSQL), which can prevent the use of the index - which won't stop the query from returning the correct results but will cripple performance.

From MSDN

SqlDateTime Structure

Represents the date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds to be stored in or retrieved from a database. The SqlDateTime structure has a different underlying data structure from its corresponding .NET Framework type, DateTime, which can represent any time between 12:00:00 AM 1/1/0001 and 11:59:59 PM 12/31/9999, to the accuracy of 100 nanoseconds. SqlDateTime actually stores the relative difference to 00:00:00 AM 1/1/1900. Therefore, a conversion from "00:00:00 AM 1/1/1900" to an integer will return 0.

Peter Wone
  • 17,965
  • 12
  • 82
  • 134
  • 1
    Loved your explanation but there is one thing wrong. SqlDateTime assignment to a DateTime does not require an explicit cast. That's what the Value Property is for – Manuel Zelenka Jun 03 '15 at 03:44
  • "SqlDateTime is wider than DateTime?" I'm not sure if that's correct. You mean in terms of memory usage or in terms of value it can accommodate? – ANewGuyInTown Jun 03 '15 at 04:10
  • @ManuelZelenka - you are correct that SqlDateTime does not require an explicit cast to DateTime. A cast is not exactly the same as a conversion, and a conversion is what the SqlDateTime.Value getter method does. I have corrected the answer text. – Peter Wone Jun 03 '15 at 06:52
  • @ANewGuyInTown - I have added some information from MSDN to clarify this point. You are right, strictly speaking DateTime is wider. But due to the fundamentally different internal representations conversions in *both* directions are potentially lossy. – Peter Wone Jun 03 '15 at 06:53