问题描述
我昨天用以下代码设置了一张桌子.代码运行时没有报告错误消息,并且该表在对象资源管理器中正确设置.
I set up a table yesterday with the following code. the code ran with no error messages reported and the table appeared correctly set up in object explorer.
Create Table PriceTable
(Airport_IACO_Code Varchar (4) NOT NULL,
Airline_IACO_Code Varchar (3) NOT NULL,
FlightDate Date NOT NULL Default Getdate(),
DepTime Time NOT NULL Default DATEADD(hour, 6, GETDATE()),
Price Smallmoney,
RouteDiscontinuedOrCommences Varchar (15),
)
GO
但是在今天检查表格时,具有 Getdate()
默认值的 FlightDate
显示昨天的日期
However on checking the table today the FlightDate
which has the Getdate()
default is showing yesterdays date
和
具有 DateAdd
默认值的 DepTime 列显示不正确的时间 18:45:02.我写这篇文章的当前时间是 11.04.
the the DepTime column which has the DateAdd
Default is showing an incorrect time of 18:45:02. the current time as I am writing this is 11.04.
有谁知道出了什么问题.
Does anyone know what is wrong.
预先感谢您提供的任何帮助.
Thanks in advance for any help offered.
推荐答案
您可能会发现在 SQL Server 中处理默认值有点违反直觉.语法是:
You may find the handling of defaults a bit counter-intuitive in SQL Server. The syntax is:
DEFAULT constant_expression
碰巧的是,SQL Server 扩展constant_expression
的定义以包括非确定性标量函数,例如 getdate()
.这些函数在每次调用时都会返回不同的值,即使参数相同.文档中的定义是:
It so happens that SQL Server extends the definition of constant_expression
to include non-deterministic scalar functions, such as getdate()
. These are functions that return a different value each time they are called, even with the same arguments. The definition in the documentation is:
只有一个常量值,如字符串;标量函数(系统、用户定义或 CLR 函数);或者可以使用NULL作为默认设置.
但是,SQL Server 没有将定义扩展到此类函数的表达式.而是在创建表并插入常量值时计算表达式.
However, SQL Server does not extend the definition to expressions of such functions. Instead, the expression is evaluated when the table is created and a constant value is inserted.
不幸的是,完成您想要的操作的一种方法是使用触发器.或者,您可以将该值保留为 NULL
并创建一个计算列来计算六小时后的日期:
Unfortunately, one way to accomplish what you want is using a trigger. Alternatively, you could leave the value as NULL
and create a computed column to calculate the date six hours hence:
create table . . .
_DepTime time,
DepTime as (cast(dateadd(hour, 6 _DepTime) as time) )
这篇关于使用 Getdate 和 DateAdd 的默认值不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!