本文介绍了使用 Getdate 和 DateAdd 的默认值不起作用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我昨天用以下代码设置了一张桌子.代码运行时没有报告错误消息,并且该表在对象资源管理器中正确设置.

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 的默认值不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-02 23:22
查看更多