Handle "DATE" datatypes with MySQL and MariaDB

Related articles and resources

• KB-LJW-DB-105 : Connect to MariaDB databases via ADO + ODBC
• KB-LJW-DB-101 : Impact of ADO cursor types on an ODBC driver in ASP

Dates and MySQL: a failed marriage

Historically, MySQL has always favored error tolerance rather than data strictness. This choice gradually led to permissive date and time handling: acceptance of invalid values (0000-00-00, month or day set to zero), silent implicit conversions, or automatic replacement of missing values. Because of this historically permissive nature of the MySQL engine, these practices did not cause blocking errors, but only simple warnings, often ignored by developers at the ASP application layer. After all, as long as it works...

This permissiveness has lasting consequences, both on the data and on the performance required to access it, and even on its quality. Many schemas were designed to rely on "sentinel dates" (0000-00-00 00:00:00) supposed to represent the absence of a value, instead of using the only reliable value: NULL.

As a consequence, many client interfaces and connectors (ODBC, PHP, etc.) had to implement specific behaviors to handle these invalid pseudo-dates, either by transforming them or by generating runtime errors. Beyond technical non-compliance, these values more directly raise the real question of their nature: do they mean "unknown", "not applicable", "error" or "real value"? However, data whose quality you cannot determine is by nature unusable, and therefore useless to store. This goes completely against the very purpose of storing it in a database.

MySQL has attempted over time to correct these original deviations by introducing strict SQL modes (sql_mode), actually enforced CHECK constraints, and better compliance with the SQL standard. However, these mechanisms remain optional and arrived late, leaving de facto a situation with many production databases still mixing legacy behaviors (permissive modes, invalid historical data) with modern robustness expectations.

MariaDB, for its part, has learned from MySQL's mistakes, and has chosen to be much more reliable and compliant with SQL standards, while offering some permissiveness on request for special cases, in order to allow gradual transitions on substantial databases and source code bases.

This article provides you with effective workaround techniques to handle the most delicate cases, as well as best practices such as defining strict schemas, rejecting invalid dates on write, reliably reading incorrect dates, correctly using NULL, and consistently configuring the server and clients, namely the ODBC Driver used to provide data to your Classic ASP application.

The moment of explosion

Start by taking a deep breath: in the vast majority of cases, if your schema and data are correct, your Classic ASP code will behave identically with your MariaDB database as with MySQL. If in doubt, you can make sure of this by checking the validity of date comparisons (e.g. >=) in some of your SQL queries, since MariaDB respects types more strictly than MySQL.

Thank MySQL

The majority of errors you may encounter generally occur when migrating an old MySQL database to MariaDB. The main cause in 98% of cases: MariaDB refuses invalid dates that MySQL had accepted until then (e.g. 0000-00-00). MySQL long tolerated these data even though they are invalid according to the SQL standard.

Your Classic ASP application works well with your historical MySQL database, then you convert it to MariaDB. And then everything explodes! You blame MariaDB and complain about its bugs, but in reality, that is not the case: the solutions are fortunately close at hand and easy. The effective response consists of cleaning your data, clarifying the schema, and sometimes minimally adjusting the application code on the Classic ASP side.

MariaDB does not create the problem, it exposes it. In this respect, thank MySQL and its historically permissive practices. Where you thought it was doing you a favor, it was merely giving you its blessing when you were doing very bad things with your data. It should have warned you, but it preferred to let you store incorrect and nonexistent dates.

Bad practices inherited from the past explain the frequency of date and time-related bugs during migrations, imports, exports, or simply when consuming data through modern, standards-compliant ODBC Drivers.

This data migration acts as a revealer of historically incorrect data, especially regarding dates. The presence of this incorrect data consequently alerts you to areas for improvement in your schema and/or your ASP code.

Healthy management of dates and times in MySQL and MariaDB is not a nicety, but a guarantee of reliability for your application on the one hand, and for the data you take great care to store on the other hand.

Tips and best practices

The following tips and best practices allow you to deal with the most frequently encountered potentially blocking situationswhen you manage dates in MySQL/MariaDB databases, and use them from Classic ASP.

Classic ASP is in no way at fault here, no more than the MySQL or MariaDB ODBC Driver: almost all the problems discussed relate to the design of the database itself. Once your schema has been corrected and your historical data cleaned, you can sleep peacefully 🛌.

This guide is comprehensive and covers a multitude of cases: there is generally little reason for you to need to apply everything, and only a few of these tips will usually be enough to solve the small bugs you are facing.

We are experienced with special cases and at your service to assist you with your Classic ASP code during your migrations, improvements, or difficult bug resolution.

Tips when designing the schema

The key point is: Rationalize your schema.

The schema of your database defines all the rules that will apply when it is used for reading and writing. The more complete and well thought out this schema is, the more predictable your application is, and the more reliable your data is.

You will be able to work around almost all of these strictness problems and bypass them: our article details the available possibilities. Nevertheless, keep in mind that, although this is technically functional, these workarounds are only intended to be temporary, because the underlying problem remains: incorrect dates/times exist in the database, and solving the problem at the source is your best guarantee. Simply wanting to work around them and ignore them will otherwise force you to code very many, too many, checks in Classic ASP to manage incorrect dates, whereas the best way to defend yourself against this enemy is to ... no longer have to face it at all.

Conforming your database is generally not a long task. Once the dates have been cleaned and the schema clarified, MariaDB proves much more predictable than MySQL, which allows your Classic ASP application to gain durability and, above all, stability. And in many cases, performance as well.

Store dates in DATETIME

When you define the structure of your table, choose the DATE type or DATETIME. You can choose TIMESTAMP if you explicitly need automatic time zone management.

Never store a date in a VARCHAR field, at the risk of not being able to filter or compare these fields efficiently on the one hand, and of drastically slowing down performance on the other hand.

Do not store dates in VARCHAR

Storing your date/time data in a VARCHAR field is a guarantee of problems today or tomorrow. This prevents you from performing efficient comparisons and sorts, or forces you to systematically use the STR_TO_DATE() function in your read queries, which greatly degrades the performance of your queries.

Example of bad practice:

-- Read from the "d_date_txt" field of type VARCHAR.
-- Attempt to convert the string to a date according to the specified pattern:
----- Will fail on the first piece of data that does not match the pattern!
SELECT
   STR_TO_DATE(d_date_txt, '%Y-%m-%d %H:%i:%s') AS d_date_safe
FROM myTable;

Define a default value

NULL

The NULL value is the only default value you should use if no date exists. It is standard, reliable, and future-proof.

Why NULL is your new best friend:

•  It complies with the SQL standard
•  It represents the real absence of a value
•  It is natively supported by MariaDB
•  It is compatible with strict SQL modes (STRICT_TRANS_TABLES, NO_ZERO_DATE)

Example of good practice:

-- Table creation
-- Use a NULL value by default => Valid with MySQL and MariaDB
CREATE TABLE contactRequests (
  d_dateReceived DATETIME NULL DEFAULT NULL
);

CURRENT_TIMESTAMP

When a field is intended to always contain the date it was added or last updated, there is no need to calculate and build the current date in your ASP code: delegate this task to the MySQL/MariaDB engine by defining the default value of your field as CURRENT_TIMESTAMP. In this way, the current date will be used as the value at the time of insertion (INSERT INTO) or update (UPDATE).

Examples of definition in your schema:

-- Table creation
CREATE TABLE contactRequests (
   d_dateReceived DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Alter an existing table
ALTER TABLE contactRequests MODIFY d_dateReceived DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

When writing to your table, you must either explicitly provide CURRENT_TIMESTAMP as the value for this field, or completely omit this field from your query and provide no value: the engine will handle it implicitly. Do not specify NULL, which will be rejected.

Examples at the time of writing:

-- Do not specify the "d_dateReceived" field:
-- it is automatically populated with the current date.
INSERT INTO contactRequests (s_firstname, s_lastname)
VALUES ('John', 'Duff');

-- Explicitly provide CURRENT_TIMESTAMP
-- (redundant and unnecessary, but functional)
INSERT INTO contactRequests (s_firstname, s_lastname, d_dateReceived)
VALUES ('John', 'Duff', CURRENT_TIMESTAMP);

-- Explicitly provide NULL
-- => CAUSES AN ERROR!
INSERT INTO contactRequests (s_firstname, s_lastname, d_dateReceived)
VALUES ('John', 'Duff', NULL);

0000-00-00 00:00:00

It is technically possible for you to use '0000-00-00 00:00:00' as the default date. At least that is what MySQL has led you to believe until now. But do not do it (otherwise stop reading this article). The various negative consequences this entails should make you prefer NULL from every point of view.

A few facts about your enemy '0000-00-00 00:00:00' are necessary to convince you:

•  It is a legacy MySQL-specific behavior intended to allow historical compatibility.
•  It is not a valid date: it does not exist in the calendar.
•  It is problematic for exchanging your data with other APIs, connectors, ORMs, or exports.
•  It does not provide information about the real state of the data: not received? unknown? error? default value? historical value? If you really need an explicit “not yet received” state, add a status column instead of misusing the date field.
•  It disrupts comparisons and sorting.
•  It returns errors in "strict" sql_mode modes (e.g. NO_ZERO_DATE, NO_ZERO_IN_DATE).
•  '0000-00-00 00:00:00' is a value to avoid except under absolute constraint. In that case, you will then have to "work around" these invalid data in order to be able to use them from your ASP code. The use of OPTION Flags will then be required.

Example of bad practice:

-- Table creation
-- Using an invalid date by default => historically tolerated by MySQL
CREATE TABLE contactRequests (
   d_dateReceived DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
);

Make the date mandatory

You can make a date field mandatory, in order to prohibit any empty, NULL, or default value, including CURRENT_TIMESTAMP. To do this, you must define no default value for this field, and prohibit NULL.

Your real objective is therefore: “The date must come from the ASP application, not from the schema”.

Examples of definition in your schema:

-- Create the table
---- d_dateReceived is mandatory
---- NULL is forbidden
---- no default value is defined
---- Any INSERT/UPDATE query must explicitly provide d_dateReceived, otherwise it fails.
CREATE TABLE contactRequests (
  d_dateReceived DATETIME NOT NULL
);

-- Alter the existing table
-- Will fail if even one row still contains NULL in d_dateReceived => Clean the table first.
---- d_dateReceived is mandatory
---- NULL is forbidden
---- no default value is defined
---- Any INSERT/UPDATE query must explicitly provide d_dateReceived, otherwise it fails.
ALTER TABLE contactRequests
MODIFY d_dateReceived DATETIME NOT NULL;

From this point on:

•  Any insertion without specifying a value for the column causes an error
•  Any insertion with NULL causes an error
•  No automatic CURRENT_TIMESTAMP value is applied by default

Examples when writing:

-- Example with no value provided:
-- The query fails.
INSERT INTO contactRequests (s_firstname, s_lastname)

-- Example with a valid value:
-- The query is accepted.
INSERT INTO contactRequests (s_firstname, s_lastname, d_dateReceived)
VALUES ('John', 'Duff', '2026-09-01 14:35:12');

-- Example of a query with CURRENT_TIMESTAMP:
-- CURRENT_TIMESTAMP is used here as an explicit value, not the default.
-- The query is accepted.
INSERT INTO contactRequests (s_firstname, s_lastname, d_dateReceived)
VALUES ('John', 'Duff', CURRENT_TIMESTAMP);

In addition to making your field mandatory, you may want to constrain the value that will be accepted by your field. Then use the SQL CHECK instruction: an automatic constraint will be applied to each INSERT and UPDATE. Remember on this topic that the CHECK constraint is actually enforced by MariaDB, unlike older versions of MySQL. Applying this constraint therefore assumes, on the one hand, that you execute your table creation/alteration query with a strict sql_mode to avoid silently corrected insertions, and, on the other hand, that you have already cleaned the invalid dates from your table. In general, always clean your data before modifying your schema, because the latter applies immediately to already existing data.

Examples of definition in your schema:

-- Create the table
---- d_dateReceived is mandatory
---- NULL is forbidden
---- no default value is defined
---- Any INSERT/UPDATE query must explicitly provide d_dateReceived, otherwise it fails
---- Additional constraint: Dates prior to 2012 are prohibited
CREATE TABLE contactRequests (
   d_dateReceived DATETIME NOT NULL,
   CONSTRAINT chk_dateReceived
      CHECK (d_dateReceived >= '2012-01-01')
);

-- Modify the existing table
-- Will fail if even one row still contains NULL in d_dateReceived => Clean the table first.
---- d_dateReceived is mandatory
---- NULL is forbidden
---- no default value is defined
---- Any INSERT/UPDATE query must explicitly provide d_dateReceived, otherwise it fails
---- Additional constraint: Dates prior to 2012 are prohibited
ALTER TABLE contactRequests
ADD CONSTRAINT chk_dateReceived
CHECK (d_dateReceived >= '2012-01-01');

Tips when importing data

Importing your MySQL database into MariaDB is generally transparent. However, if you encounter an error on a field of type DATE, DATETIME, or TIMESTAMP, it means that the MariaDB engine is encountering incorrect dates.

An invalid date cannot exist in a DATE/DATETIME column if sql_mode is strict. Some workaround techniques (e.g. temporary adjustment of sql_mode) are presented here to allow you to force MariaDB to import your "dirty" data without receiving any complaints from it.

Afterwards, once your data has been imported, you can then clean it, then finish by adjusting your schema to guarantee the health of future data.

Import using sql_mode

The MySQL/MariaDB engine makes it possible to apply different levels of strictness. The SQL mode sql_mode is intended for this purpose. It may be tempting to add exceptions to sql_mode during the ODBC connection in order to use or even abuse this mode to "make do" with incorrect data when reading, or to "be lax" when writing, but this is a very bad idea.

This SQL mode must be used as a temporary workaround during an import session, before ultimately cleaning the data and rationalizing your schema. This SQL mode must no longer be used in production during reading/writing via the INITSTMT attribute of your connection string, unless absolutely necessary.

The SQL mode can be specified in different ways, with a different scope:

•  Permanent definition (my.ini configuration file):
  It guarantees you a systemic constraint in production.
  INI

  [mysqld]
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE

  
  
•  Definition during import:
  It applies to the current connection. Use it when importing your data from phpMyAdmin, Navicat, HeidiSQL, etc.
  SQL

  SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';

  
  
•  Definition in your ODBC connection:
  You can define the SQL mode within the INITSTMT attribute of your connection string: this attribute allows the ODBC Driver to execute an SQL statement when the connection is opened.
  ASP CLASSIC

  <%
  'Declare the database connection string via the MariaDB Driver
  '(with a custom sql_mode)
  Dim dbConnString : dbConnString = "Driver={MariaDB ODBC 3.2 Driver}; Server=127.0.0.1; Charset=utf8; Port=3306; Database=dbName_abcdef; User=dbUser_test_abc123; Password=dbPw_abcdef12345; InitStmt={SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';}"
  %>

  
  The SET SESSION sql_mode is executed on each connection, and is guaranteed only for that connection. The greatest risk is that a connection pool may reuse a session with another sql_mode. In addition, during a later SQL query in your code, a subsequent SET sql_mode may overwrite it.

Remember once again that adjusting the SQL mode at the highest level, namely in your ASP application code during your ODBC connection, is discouraged, and does not necessarily have sufficient scope. This sql_mode is rather intended to be adapted temporarily during your data imports, therefore in the queries you execute from phpMyAdmin or from your client management software (e.g. HeidiSQL, Navicat, etc.). Once your data has been imported (including historically incorrect dates), clean it.

Following your use of SQL mode for workaround purposes during your migrations, strict mode should be preferred globally and permanently, and defined at the lowest level, namely in the my.ini configuration file in the [mysqld] section.

Clean up after import

 WARNING: Cleaning up your database involves schema changes, data replacements, and record deletions. Before any operation, make sure to create backups/backups/DUMP of your databases. It is also recommended to create them at each important step of your cleanup operations. You will then be able to roll back in case of an error, without losing your previous optimizations.

Note that the SELECT and UPDATE queries presented below in this section will work only if sql_mode does not already prohibit zero dates at execution time, otherwise the operation will fail.

After importing your data from MySQL, make sure to clean it up.

Search for invalid dates (zero dates)

-- The d_dateReceived field is of type Date.
-- The threshold 1000-01-01 covers dates that are technically storable,
-- but invalid according to the SQL standard.
SELECT *
FROM contactRequests
WHERE d_dateReceived = '0000-00-00 00:00:00'
   OR d_dateReceived < '1000-01-01';

It is up to you at this point to decide what you want to do with this incorrect data:

• either delete the affected rows
• or replace them with a real business date (e.g. creation date, order date, etc.)
• or replace them with NULL

Replace incorrect dates with NULL (zero dates)

UPDATE contactRequests
SET d_dateReceived = NULL
WHERE d_dateReceived = '0000-00-00 00:00:00'
   OR d_dateReceived < '1000-01-01';

Modify the schema (zero dates)

You must now force the default value to NULL (or CURRENT_TIMESTAMP).

In general, always clean your data before modifying your schema, because the latter applies to already existing data.

Tips when writing data

Providing MySQL/MariaDB with clean and valid dates is the guarantee that you will not have to handle invalid dates in the future. The following tips allow you to make sure that your Classic ASP code systematically sends a valid value to the SQL engine.

Do not provide an empty value

In your Classic ASP code, make sure to never send an empty string to a field of type DATE or DATETIME, but always at least the string "NULL", the only acceptable value as a default value if your table schema specifies NULL for this field. If you use a parameterized ADODB.Command query, specify adDate or adDBTimeStamp as the variable type, or provide it with the VBScript variant type Null.

If you want the current date to be stored, then define CURRENT_TIMESTAMP as the default value in your table schema for this field. The NULL value will then be rejected.

<%
'Specify NULL using a standard SQL query
'-	-	-	-	-	-	-	-	-	-	-	-
strSQL = "INSERT INTO contactRequests (d_dateReceived) VALUES (NULL)"
dbConn.Execute(strSQL)


'Specify NULL using a parameterized SQL query
'-	-	-	-	-	-	-	-	-	-	-	-
'Do not use vbNull: it is an internal VBScript constant type
Set paramCmd = Server.CreateObject("ADODB.Command")
Set paramCmd.ActiveConnection = dbConn
paramCmd.CommandText = "INSERT INTO contactRequests (d_dateReceived) VALUES (?)"
paramCmd.Parameters.Append paramCmd.CreateParameter("@d_dateReceived", adDBTimeStamp, adParamInput, , Null)
paramCmd.Execute
%>

Provide a date in ISO format

The safest way to provide a date in your query to MySQL/MariaDB is to provide it with an ASP/VBScript variable of type Date, then inject it into your query using a parameterized query via ADODB.Command by explicitly forcing the ADO type to adDate or adDBTimeStamp. The data type is therefore explicit, and you do not have to perform any date/time conversion or formatting on the ASP side. Parameterized queries also protect you against SQL injection risks.

If you cannot use a parameterized query, build the date string that will be provided to MySQL/MariaDB in ISO formatYYYY-MM-DD HH:MM:SS, using the following code:

<%
'Return a date formatted for MySQL/MariaDB in the format "YYYY-MM-DD HH:MM:SS"
Function DateFormat_MySQL_MariaDB(byVal dtInput)

   'Parameter  Variable   Type          Description
   '---------  --------   ----          -----------
   '@param     dtInput    String/Date   Date or Date/Time (VBScript/ASP type "String" or "Date")
   '@return    dtOutput   String/Null   Date string formatted as "YYYY-MM-DD HH:MM:SS", or Null.

   Dim sYear, sMonth, sDay, sHour, sMinute, sSecond
   Dim dtOutput : dtOutput = Null
   
   'If the input can be converted to type "Date":
   If (IsDate(dtInput)) Then
      'Retrieve the date parts
      sYear   = CStr(Year(dtInput))
      sMonth  = Right("0" & Month(dtInput), 2)
      sDay    = Right("0" & Day(dtInput), 2)
      
      'Check if a time is present
      If ((Hour(dtInput) + Minute(dtInput) + Second(dtInput)) > 0) Then
         sHour   = Right("0" & Hour(dtInput), 2)
         sMinute = Right("0" & Minute(dtInput), 2)
         sSecond = Right("0" & Second(dtInput), 2)
      Else
         sHour   = "00"
         sMinute = "00"
         sSecond = "00"
      End If
      
      'Build the date string in ISO format for MySQL/MariaDB
      dtOutput = sYear & "-" & sMonth & "-" & sDay & " " & sHour & ":" & sMinute & ":" & sSecond
   Else
      dtOutput = Null 'Return Null if the input is not a valid date
   End If

   'Return
   DateFormat_MySQL_MariaDB = dtOutput

End Function 'DateFormat_MySQL_MariaDB



'= =  =  =  =  =  =
'Usage examples
'= =  =  =  =  =  =

Dim dateSource, datetimeSource, dateStringForSQL

'Ex. Handling a date in English
'-------------------------------
SetLocale(1033) 'Set LCID to English

'Set/read a source date (in English format) - of type vbString.
dateSource = "04/13/2026" 'ex. April 13

'Return the date, formatted for MySQL/MariaDB
'=> RETURNS : 2026-04-13 00:00:00
dateStringForSQL = DateFormat_MySQL_MariaDB(dateSource)


'Ex. Handling a date in French
'-----------------------------
SetLocale(1036) 'Set LCID to French

'Set/read a source datetime (in French format) - of type vbDate.
datetimeSource = #01/09/2026 14:35:12# 'ex. 1 septembre

'Return the date, formatted for MySQL/MariaDB
'=> RETURNS : 2026-09-01 14:35:12
dateStringForSQL = DateFormat_MySQL_MariaDB(datetimeSource)


'Define the database insertion query.
'Use the date already correctly formatted for MySQL/MariaDB.
strSQL = _
   "INSERT INTO contactRequests ( " & _
      "s_firstname, " & _
      "s_lastname, " & _
      "d_dateReceived " & _
   ") VALUES ( " & _
      "'John', " & _
      "'Duff', " & _
      "STR_TO_DATE('" & dateStringForSQL & "') " & _
   ") "

'Add to the database
dbConn.Execute(strSQL)
%>

Provide a date in a custom format

When you have an ASP/VBScript variable that is neither of type vbDate, nor a vbString string containing a date in an ISO format understandable by MySQL/MariaDB, you must then provide the database engine with instructions about the format (pattern) of your date, so that it can parse it according to that pattern. You can use different markers in your pattern (e.g. %Y %m %d %H %i %s etc.).

This technique allows MySQL/MariaDB to know how to translate your character string into a real date that it will be able to store. To do this, use the following native SQL function: STR_TO_DATE().

<%
'Define a datetime with an arbitrary/exotic format – of type vbString.
Dim datetimeSource : datetimeSource = "TheYear=2026 TheMonth=09 TheHour=14 TheSecond=12 TheDay=01 FinallyTheMinute=35"

'Define a mask/pattern that allows MySQL/MariaDB to understand the provided string.
'(contains placeholders %Y %m %d %H %i %s, that are recognized by MySQL/MariaDB)
Dim datetimeMask : datetimeMask = "TheYear=%Y TheMonth=%m TheHour=%H TheSecond=%s TheDay=%d FinallyTheMinute=%i"

'Define the insertion query
strSQL = _
   "INSERT INTO contactRequests (" & _
      "s_firstname, " & _
      "s_lastname, " & _
      "d_dateReceived " & _
   ") VALUES (" & _
      "'John', " & _
      "'Duff', " & _
      "STR_TO_DATE('" & datetimeSource & "', '" & datetimeMask & "') " & _
   ")"

'Add to the database
dbConn.Execute(strSQL)
%>

Note, however, that you depend here on a fixed format: the slightest variation in the string will cause your query to fail, because STR_TO_DATE() will not understand it. This technique strongly exposes you to SQL injections if datetimeSource or other values become dynamic. Always prefer parameterized queries. Contact us if you need to implement parameterized queries effectively in your ASP source code; we master all the subtleties needed to implement them properly.

Let the SQL engine use the current date

If you need to store the current date at the time of insertion (INSERT INTO) or update (UPDATE), rely on the default value of your field. This avoids having to build the date in your ASP code: you delegate this task to the MySQL/MariaDB engine.

See this section to implement this technique.

Tips when reading data

Reading dates from a MySQL/MariaDB database seems like a simple operation. However, depending on whether your database may contain historically incorrect dates, or whether you want to read them in a particular format, certain precautions and techniques should be considered.

Use parameterized queries

Any creation of an SQL query from your ASP code involves a significant risk of SQL injection if you concatenate strings, variables, and data, especially if some of this data has been entered by a user and is not under your control.

To protect yourself against SQL injections, use parameterized queries via ADODB.Command instead, specifying each parameter as ?. Remember to force the ADO type to adDate or adDBTimeStamp instead of letting ADO guess: this limits implicit conversions and silent errors.

Handle dirty values

If you have doubts about the cleanliness of the table schema or the data it contains, add safeguards in your SQL queries to avoid running into dirty and incorrect data, or returning it to your ASP application. Note that these practices allow you to work around an invalid date, but slow down the execution of your queries. Cleaning your data at the source remains the best practice.

These queries therefore allow you to "make do" with historically incorrect data, but they have a cost because of the filtering they perform. They are therefore a "bandage", if you have no other choice but to deal with historically dirty and incorrect data, and you have not yet performed a cleanup.

-- VARIANT 1 : More concise
-- Performance is equivalent between CASE and NULLIF, even on large volumes.
-- CAST() has no impact in terms of performance.
-- *  *  *  *  *
-- Read from the "d_date" field of type DATETIME
-- Ensure that the data is a valid date
-- Returns NULL otherwise
SELECT
   CAST(
      NULLIF(
         IF(d_date < '1000-01-01', NULL, d_date),
         '0000-00-00 00:00:00'
      )
   AS DATETIME) AS d_date_safe
FROM contactRequests

-- VARIANT 2 : More readable
-- Performance is equivalent between CASE and NULLIF, even on large volumes.
-- CAST() has no impact in terms of performance.
-- *  *  *  *  *
-- Read from the "d_date" field of type DATETIME
-- Ensure that the data is a valid date
-- Returns NULL otherwise
SELECT
   CAST(
      CASE
         WHEN d_date = '0000-00-00 00:00:00' THEN NULL
         WHEN d_date < '1000-01-01'          THEN NULL
         ELSE d_date
      END
   AS DATETIME) AS d_date_safe
FROM contactRequests

The most delicate case is that of dates and times stored in a VARCHAR field. The following code shows you how to handle this type of case in a SELECT clause, but remember that filtering or comparing on these fields in a WHERE clause is almost impossible, and may drastically slow down performance as well.

-- Read from the "d_date_txt" field of type VARCHAR
-- Attempt to convert the string to a date according to the specified pattern
----- Will fail at the first instance of data that does not match the pattern!
SELECT
   STR_TO_DATE(d_date_txt, '%Y-%m-%d %H:%i:%s') AS d_date_safe
FROM myTable;

Finally, if your data is clean and your schema specifies NULL as the default value, then all your rows are healthy. Your query can then be as simple and short as this:

SELECT d_date FROM myTable WHERE d_date IS NOT NULL

Read a date in a custom format

When you want to read a date in a particular, exotic, or localized format, you can use the MySQL/MariaDB DATE_FORMAT() function, as follows:

<%
'We want to retrieve the date in the following exotic format: YYYY/MM hh:ss
'   Using the DATE_FORMAT() function slows down your query.
'   Therefore, it will be more efficient to read the raw data from MySQL/MariaDB,
'   then use ASP/VBScript to extract parts from it, then format it back.
Dim dateMask : dateMask = "%Y/%m %H:%i"

'Define the reading/retrieval request
Dim strSQL : strSQL = "SELECT DATE_FORMAT(contactRequests.d_dateReceived, '" & dateMask & "') AS myDate FROM contactRequests "

'Open database connection
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open dbConnString

'Open the Recordset in "Forward-Only" mode via .Execute()
Set rs = dbConn.Execute(strSQL)
%>

Work around zero dates

First, this advice is temporary. You can temporarily disable strict checks on the driver side via the OPTION parameter in your connection string, but note that this is NOT a long-term solution: only a workaround around your invalid data.

Working around (ignoring) invalid data when reading is a bandage. The approach you must follow is the following:

• After import: Correct your data to NULL, then adjust your schema by defining NULL as the default value,
• When writing: Normalize the data you provide to your SQL query.

During a data migration from MySQL to MariaDB, MariaDB is stricter and refuses to import your table if it contains empty dates 0000-00-00 00:00:00. However, if you forced their import but forgot to clean them up, then this incorrect data is still there.

When your table contains records with invalid dates (e.g. 0000-00-00 00:00:00), ADO may forward you an error message from the MySQL/MariaDB engine, complaining about the presence of such empty dates. Our best advice at this stage is to clean your data.

However, if you really want to allow your ASP application to access this data, you can then use the values of certain Flags in the OPTION parameter of your connection string in order to read from this table without encountering an error on the ASP side. In practice, this amounts to asking the ODBC Driver to behave as MySQL historically did, even if MariaDB is stricter.

These options must be used temporarily to ensure compatibility of your Classic ASP application with a database containing invalid dates inherited from MySQL, but they must not be considered a definitive solution.

References for the OPTION parameter:

• Reference & Full MySQL list:
  • https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-flags 
• References & Incomplete MariaDB lists (some MySQL parameters may still be supported, without guarantee):
  • https://mariadb.com/docs/connectors/mariadb-connector-odbc/mariadb-connector-odbc-guide#general-connection-parameters 
  • https://docs.skysql.com/Connecting%20to%20SkySQL%20DBs/Connect%20using%20ODBC/#options-bitmask 

•  Relevant Flags
  • 16777216: Allows invalid dates (e.g. 0000-00-00, 0000-00-00 00:00:00) without a driver-side error.
  • 33554432: Disables certain additional strict checks on date and timestamp conversions.
•  Recommended use
  • Use these Flags temporarily, then:
    1. Clean invalid dates in your database,
    2. Adapt the schema of your table (e.g. DATETIME NULL),
    3. Gradually free yourself from the OPTION parameter.
•  When to use them
  • Urgent migration without immediate possibility of cleaning your data
  • Technical diagnostic or audit phase
  • Frozen source code that is difficult to modify
•  When NOT to use them
  • During new development
  • After the data migration phase
  • As a definitive solution, otherwise you institutionalize technical debt in your application
•  Important limitations
  • These options do not correct the data
  • They mask functional inconsistencies
  • They make behavior dependent on the ODBC Driver
  • They reduce database portability and the reliability of your data
•  Immediate effects (but temporary)
  • The import or execution of existing Classic ASP code no longer generates an error
  • Old databases are accepted by MariaDB without immediate modification of the data

Classic ASP hosting

Classic ASP hosting and migration to Windows Server 2025 / 2022 / 2019

Classic ASP development & maintenance

Classic ASP maintenance, support and development for websites and business applications

IIS Audit & configuration tweaking

Microsoft® IIS configuration audit and performance tuning