Default and future date/time values in SQL Server

SQL Server can generate current or future date/time values itself, or Classic ASP can supply a typed date value through an ADO parameter. For new schemas, datetime2 is generally preferable to the older datetime/smalldatetime types because it offers a wider range and configurable fractional-second precision.

Insert ASP Now() values with parameters

The historical page concatenated formatted dates directly into SQL text. That can become locale-sensitive. Pass date values as parameters instead.

<%
Dim dtm, dtm2, cmd
dtm = Now()
dtm2 = DateAdd("d", 15, dtm)

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO dbo.dt (book_id, issue_dt, return_dt) VALUES (?, ?, ?)"
cmd.Parameters.Append cmd.CreateParameter("pBook", adInteger, adParamInput, , 9)
cmd.Parameters.Append cmd.CreateParameter("pIssue", adDBTimeStamp, adParamInput, , dtm)
cmd.Parameters.Append cmd.CreateParameter("pReturn", adDBTimeStamp, adParamInput, , dtm2)
cmd.Execute
Set cmd = Nothing
%>

This example assumes ADO constants such as adCmdText, adInteger, adDBTimeStamp and adParamInput are available, commonly through adovbs.inc.

Use a database DEFAULT for the current timestamp

CREATE TABLE dbo.dt (
  issue_id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_dt PRIMARY KEY,
  book_id INT NOT NULL,
  issue_dt DATETIME2(0) NOT NULL,
  return_dt DATETIME2(0) NOT NULL,
  created_at DATETIME2(0) NOT NULL CONSTRAINT DF_dt_created_at DEFAULT SYSDATETIME()
);

GETDATE() remains supported and returns a datetime. SYSDATETIME() returns a higher-precision datetime2 value, making it a natural match for a datetime2 column.

Default a return date 15 days ahead

DATEADD(day, 15, SYSDATETIME())

DATEADD() adds the requested interval and returns a new date/time value.

Complete table with current and future defaults

CREATE TABLE dbo.dt (
  issue_id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_dt PRIMARY KEY,
  book_id INT NOT NULL,
  issue_dt DATETIME2(0) NOT NULL CONSTRAINT DF_dt_issue_dt DEFAULT SYSDATETIME(),
  return_dt DATETIME2(0) NOT NULL CONSTRAINT DF_dt_return_dt DEFAULT DATEADD(day, 15, SYSDATETIME()),
  created_at DATETIME2(0) NOT NULL CONSTRAINT DF_dt_created_at DEFAULT SYSDATETIME()
);

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: