SQL SERVER – Adding Datetime and Time Values Using Variables

来源:转载

Datetime datatype stores date and time part. If you have only date value, You can add time part to a datetime variable using direct literal value and + operator.

It is shown below

DECLARE @date DATETIME SET @date='2010-10-01' SET @[email protected]+'15:00:00' SELECT @date AS DATETIME

The result is 2010-10-01 15:00:00.000

But it is not possible to add it using a time variable

<span>DECLARE </span><span>@date </span><span>DATETIME</span><span>, </span><span>@time </span><span>time<br /> </span><span>SET </span><span>@date</span><span>=</span><span>'2010-10-01'<br /> </span><span>SET </span><span>@time</span><span>=</span><span>'15:00:00'<br /> </span><span>SET </span><span>@date</span><span>=</span><span>@date</span><span>+</span><span>@time<br /> </span><span>SELECT </span><span>@date</span>

The error is

Msg 402, Level 16, State 1, Line 4The data types datetime and time are incompatible in the add operator.

So the solution is to convert time datatype into datetime and add

DECLARE @date DATETIME, @time time SET @date='2010-10-01' SET @time='15:00:00' SET @[email protected]+CAST(@time AS DATETIME) SELECT @date AS DATETIME

The result is 2010-10-01 15:00:00.000

So when adding data of datetime and time datatype values, you need to be aware of this.

Reference: Pinal Dave ( http://blog.sqlauthority.com)



分享给朋友:
您可能感兴趣的文章:
随机阅读: