使用min(datetime)理解SQL服务器行为

(long story short, was just a dumb assumption by me, I was sure a column was a datetime and it wasn't - so don't expect to find anything interesting in this question, leaving it here so that dems gets his rightfully accepted answer)

(长话短说，在我看来，这只是一个愚蠢的假设，我确信一篇专栏文章是一个日期时间，但它不是——所以别指望在这个问题中找到什么有趣的东西，把它放在这里，这样一来，*党就能得到他正确的答案。)

I wrote a simple query like this:

我写了这样一个简单的查询:

SELECT ID, MIN(DateMadeActive) AS DateMadeActive
FROM RecordStateField WHERE RecordStatusID in (2, 3)
GROUP BY ID

Where DateMadeActive is a datetime column. This returned what I expected:

DateMadeActive是一个datetime列。这回答了我所期望的:

ID  DateMadeActive
1   20/06/2011 16:15:04
2   20/06/2011 16:14:28

Now I inserted this into a new table, but inserting the result of MIN(DateMadeActive) into a datetime column of another table gave me this error:

现在我将它插入到一个新表中，但是将MIN(DateMadeActive)的结果插入另一个表的datetime列中会产生以下错误:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

So to test this I change my select to this:

为了测试这个，我将选择改为:

SELECT ID, CAST(Min(DateMadeActive) as datetime) AS DateMadeActive
FROM RecordStateField WHERE RecordStatusID in (2, 3)
GROUP BY ID

Same exception. I can write it like this and make it work:

同样的异常。我可以这样写，让它工作:

SELECT ID, CONVERT(datetime, Min(DateMadeActive), 103) AS DateMadeActive
FROM RecordStateField WHERE RecordStatusID in (2, 3)
GROUP BY ID

So I can get it working, but this confuses me. The documentation for MIN(expression) says the return type should be the same as expression, but what seems to be happening is that MIN(datetime) is returning nvarchar(255). I can confirm this by running this:

我可以让它工作，但这让我困惑。MIN(expression)的文档说，返回类型应该与表达式相同，但似乎发生的是MIN(datetime)正在返回nvarchar(255)。我可以通过如下操作来确认:

SELECT ID, Min(DateMadeActive) AS DateMadeActive
INTO TestTable
FROM RecordStateField WHERE RecordStatusID in (2, 3)
GROUP BY ID

And I can see that the DateMadeActive column is of type nvarchar(255). Anyone shed any light on this? Just a documentation bug in MSDN?

我可以看到DateMadeActive列的类型是nvarchar(255)。有人知道吗?只是MSDN中的一个文档错误?

1 个解决方案

#1

It looks to me that DateMadeActive in [RecordStateField] is a VARCHAR(255) field. Could you post the table definitions?

在我看来[RecordStateField]中的DateMadeActive是一个VARCHAR(255)字段。你能发布下表的定义吗?

I ask this because MIN() has neve rchanged the type for me before. And so if the result is a VARCHAR(255) it seems the input must also be a VARCHAR(255)

我问这个是因为MIN()以前从未为我更改过类型。因此，如果结果是VARCHAR(255)，输入也必须是VARCHAR(255)

#1