Store Classic ASP date/time values safely 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.

Pass a date as a typed parameter

VBScript example

 CopySet cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandType = 1 ' adCmdText
cmd.CommandText = "UPDATE dbo.dt SET issue_dt = ? WHERE book_id = ?"
cmd.Parameters.Append cmd.CreateParameter("pDate", 135, 1, , CDate(dateText))
cmd.Parameters.Append cmd.CreateParameter("pId", 3, 1, , 5)
cmd.Execute

Validate dateText with IsDate() first. Prefer unambiguous form data and typed ADO parameters instead of inserting locale-formatted strings into SQL text.

Date/time values should be stored in SQL Server date/time columns as typed values, not as preformatted display strings. In Classic ASP, validate user-entered dates and send them through an ADO parameter so SQL Server receives a date value rather than locale-dependent SQL text.

A valid VBScript date/time string

dtm = "8/17/2007 7:44:56 PM"

This may parse under a U.S.-style server locale, but slash-formatted strings can be interpreted differently on servers using another locale.

An invalid time

dtm = "8/17/2007 7:74:56 PM"

The minute value 74 is invalid. Passing this to CDate() or another date operation can raise a type mismatch.

Typical error

Microsoft VBScript runtime error '800a000d'
Type mismatch

Validate, convert and parameterize

 Copy<%
Dim dtm, cmd
dtm = Request.Form("issue_dt")

If IsDate(dtm) Then
Set cmd = Server.CreateObject("ADODB.Command")
  Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO dbo.dt (book_id, issue_dt) VALUES (?, ?)"
cmd.Parameters.Append cmd.CreateParameter("pBook", adInteger, adParamInput, , 9)
  cmd.Parameters.Append cmd.CreateParameter("pIssue", adDBTimeStamp, adParamInput, , CDate(dtm))
  cmd.Execute
Set cmd = Nothing
Else
Response.Write "Please enter a valid date and time."
End If
%>

This assumes ADO constants are available. Keep the value typed in the database; apply presentation formatting only when displaying it. For browser forms, an ISO-like input format or separate validated components can reduce locale ambiguity.

Avoid ambiguous date strings

Strings such as 8/17/2007 depend on regional interpretation. Parse inputs deliberately and send an ADO date/time parameter; for SQL literals in controlled scripts, use unambiguous ISO-compatible formats.

Keep presentation separate from storage

Store values as date, datetime2, or another appropriate date/time type, not as text. Convert them to display strings with CONVERT() at output time.

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: