Default and future date/time values in SQL Server

Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.

Use a server-side date default

SQL example

 CopyCREATE TABLE dbo.events (
  event_id INT IDENTITY(1,1) PRIMARY KEY,
  created_at DATETIME2(0) NOT NULL DEFAULT SYSDATETIME()
);
INSERT INTO dbo.events DEFAULT VALUES;

The database generates a creation timestamp even if an insert omits that column. It does not depend on a formatted date string sent from ASP.

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.

 Copy<%
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

 CopyCREATE 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

 CopyCREATE 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()
);

Database time and application time

GETDATE() and SYSDATETIME() use the SQL Server host’s local time. If you require UTC timestamps across servers, consider SYSUTCDATETIME() and document the time-zone convention.

Default only applies when omitted

A default constraint is used when a column is omitted from an insert or when DEFAULT is explicitly requested. Explicit NULL does not trigger the default for a non-null column.

Frequently asked questions

Are these queries for SQL Server or Access?

The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.

Can I execute these statements directly on a live database?

Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.

How should Classic ASP send input to a query?

Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.

Back to top ↑

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: