Sql Server – DataTypes – datetime2 & Legacy Apps


Customer raised a P-1.

Ticket Contents

VB retirement calc tool application is experiencing very weird behavior.
This SQL query “SELECT abflag_updatedate FROM abtbl WHERE tablename = ‘plan’ returns a datetime string and will be formatted by this VB code :
getUpdateDate = Format(rstBase.Fields.Item(“abflag_updatedate”).Value, C_DateFormat1) //C_DateFormat1=”mm-dd-yyyy”

Starting this week errors were reported by tester. And by tracing into the code, I noticed that the Format() method call does not do the formatting as expected. The string value getting from the query of a datetime is like “2017-09-20 22:44:26.890” which is ignored by the Format function.

Production application is working, we verified that.

I suspect that the SQL query in VB code / Recordset was getting different format of string in the past and in production at of now so the Format function was working. Please help investigate this issue, to see if there was SQL server configuration change that made the impact.

I am concerned that this issue will impact a lot of places in the VB applications as I see this type of code everywhere. And we should be aware of the root cause so it will NOT apply to Prod.


Did not have Visual Basic, but developer gave enough details for a clean room implementation.


Dim C_DateFormat1 as string


Do While NOT Recordset.Eof   

 dtABFlag = Format(rstBase.Fields.Item("abflag_updatedate").Value, C_DateFormat1)  


DB Changes

Made a slight structural change to db, created a new column, abflag_updatedateAsDatetime.

Notice original column’s datatype is datetime2(3).

New column’s datatype is datetime.

If it were MTV’s Real World, would have gone with a computed column.


Clean Room Implementation

DB_CONNECTION_STRING = "Provider=sqloledb;Data Source=DBSERVER;Initial Catalog=rbp;Integrated Security=SSPI;"

'declare the SQL statement that will query the database

rem SQL = "SELECT abflag_updatedate FROM dbo.abtbl WHERE tablename = 'plan'"
SQL = "SELECT abflag_updatedate, abflag_updatedateConverted = convert(datetime,  abflag_updatedate ) FROM dbo.abtbl WHERE tablename = 'plan' "


'create an instance of the ADO connection and recordset objects


Set Connection = CreateObject("ADODB.Connection")

Set Recordset = CreateObject("ADODB.Recordset")


'open the connection to the database




'Open the recordset object executing the SQL statement and return records 

Recordset.Open SQL,Connection


'first of all determine whether there are any records 

If Recordset.EOF Then 

	wscript.echo "There are no records to retrieve; Check that you have the correct job number."


'if there are records then loop through the fields 

Do While NOT Recordset.Eof   

  field = Recordset("abflag_updatedate")

  fieldFormatted = FormatDatetime(Recordset.Fields.Item("abflag_updatedate").Value, vbShortDate )

  if field <> "" then

    wscript.echo "field " & field
    wscript.echo "fieldFormatted " & fieldFormatted

 end if
 if (Err.Number <> 0) Then 	

   Wscript.echo String(CHAR_LENGTH, CHAR_SEP)
   wscript.echo "Err.Number :- " & Err.Number
   wscript.echo "Err.Description :- " & Err.Description
   Wscript.echo String(CHAR_LENGTH, CHAR_SEP)		
 end if	

   fieldConverted = Recordset("abflag_updatedate")

   rem fieldFormatted = Format(Recordset.Fields.Item("abflag_updatedate").Value, C_DateFormat1)

   fieldConvertedFormatted = FormatDatetime(Recordset.Fields.Item("abflag_updatedateConverted").Value, vbShortDate )

  if (Err.Number <> 0) Then
	Wscript.echo String(CHAR_LENGTH, CHAR_SEP)	
	wscript.echo "Err.Number :- " & Err.Number
	wscript.echo "Err.Description :- " & Err.Description
	Wscript.echo String(CHAR_LENGTH, CHAR_SEP)				

  end if

  if fieldConverted <> "" then

    wscript.echo "fieldConverted " & fieldConverted
    wscript.echo "fieldConvertedFormatted " & fieldConvertedFormatted

  end if


End If


  1. Format Versus FormatDatetime
    • VBScript does not support the Format Function, and so please use FormatDateTime
  2. Function – FormatDatetime
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails with error
          • Error Number ( Err.Number ) :- 13
          • Error Number ( Err.Description ) :- Type mismatch
  3. Function – Format
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails silently
        • Returns passed in datetime without formatting as requested
  4. Function – CDate
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails

Source Code Control






There is always a first time.

First post under the Visual Basic Category.


Moral of the Story

Do not use new data types and if you have to, use views, stored procedures, tracking ( computed )  columns to shield from frontend.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s