Store Classic ASP date/time values safely in SQL Server

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

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

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: