Sql Server – DataTypes – datetime2 & Legacy Apps

Background

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.

TroubleShooting

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

Snippet


Dim C_DateFormat1 as string

C_DateFormat1="mm-dd-yyyy"

Do While NOT Recordset.Eof   

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

Loop

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

wscript.echo DB_CONNECTION_STRING

Connection.Open DB_CONNECTION_STRING 

 

'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."

Else 

'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)		
		
   Err.Clear
	
 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
	
  Recordset.MoveNext     

Loop

End If

Findings

  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

GitHub

DanielAdeniji/SQLServerDatatypeDatetime2LegacyApp

Link

 

Dedicated

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s