defaults using Getdate and DateAdd are not working

Question

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

However on checking the table today the FlightDate which has the Getdate() default is showing yesterdays date

and

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.

Answer 1

You may find the handling of defaults a bit counter-intuitive in SQL Server. The syntax is:

DEFAULT constant_expression

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:

Only a constant value, such as a character string; a scalar function (either a system, user-defined, or CLR function); or NULL can be used as a default.

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.

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) )