Issue with importing SQL Server date datatypes into Excel date formats

Question

I am importing data into Excel from a SQL Server 2012 database. The issue I have is that SQL Server 2012 Date datatype columns are not being recognised consistently in Excel.

If I use ADO Recordsets to import data from specific columns, data with column datatype Date or Datetime is copied into the Recordset as VBA datatype String (instead of Date or Integer).

Reproducing the Recordset in Excel yields strings in the format yyyy-mm-dd. These cells are not recognized by Excel as date/time, even if I change the formatting.

Yet when I refer to the Excel cell from another cell the Date type is recognized (eg: A1 contains the result of my SQL VBA Recordset query, for example "2013-04-17". I enter into cell A2 "=A1 + 1", A2 will display it as 41382.

However, when I use MS Query to extract data from the same Database using ListObjects and ODBC, the same data from the same database is returned with dates being interpreted correctly by Excel (ie I import a column called "Transaction Date" with SQL datatype Datetime via MS Query the result will be a MS Excel Date "Integer").

How do I amend my VBA Recordset code to treat data in the format yyyy-mm-dd as dates instead of strings?

Thanks, John

PS: SQL queries I execute vary so that sometimes I get data from columns with datatype "date" and sometimes its just integers or Double datatype. This means that hardcording "Convert(INT, TransactionDate)" into the SELECT statement is not really possible. However, I am a total amateur at SQL so there might be a super easy solution to this (eg if I ever access columns with datatype "DateTime" then SQL should always send "CONVERT(DBL, XXX)" instead of XXX where XXX is a column with datatype DATE.

Function GetSQL(strQuery As String) As Variant

Dim rst As ADODB.Recordset
Dim element As Variant
Dim i, j As Integer
Dim v As Variant
On Error GoTo aError


Call ConnecttoDB

cnt.Open
Set rst = New ADODB.Recordset
rst.Open strQuery, cnt, adOpenStatic
rst.MoveFirst
If rst.RecordCount = 0 Then   'i.e. if it's empty
    v = CVErr(xlErrNA)
    rst.Close
    cnt.Close
Else
End If
 v = rst.GetRows

For i = 0 To UBound(v, 1)
    For j = 0 To UBound(v, 2)
        If v(i, j) = -9999 Then
            v(i, j) = CVErr(xlErrNA)
        Else
        End If
    Next j
Next i
GetSQL = Application.WorksheetFunction.Transpose(v)
rst.Close
cnt.Close
Exit Function

aError:
MsgBox Err.Description
rst.Close
cnt.Close
End Function

you can add format into your ms sqlquery like select Format(datefile,'dd/mm/yyyy') from table or into you vba code : yourcell.NumberFormat = "yyyy-mm-d;@" check this question stackoverflow.com/questions/7106238/… — danywalls
– danywalls, Commented Apr 17, 2013 at 14:09
As @danywalls said in his comment, I think your best option would be to format dates in sql querys to something that excel would interpret as date. Check this link for the many date formating options you have. sql-server-helper.com/tips/date-formats.aspx — Ateszki
– Ateszki, Commented Apr 17, 2013 at 14:55
Thanks but I think because its possible to solve my problem via Querytables with the same T-SQL there has got to be a way of getting the dates to be read as Excel dates without amending the T-SQL query. — John Smizz
– John Smizz, Commented Apr 17, 2013 at 15:30
The problem you have to solve is how the getrows method converts the date values to string. — Ateszki
– Ateszki, Commented Apr 17, 2013 at 16:46

John Smizz · Accepted Answer · 2013-04-24 15:04:43Z

1

The issue arose from using the Excel function Transpose instead of looping through the contents of the Recordset results object.

In the process of Transposing via Excel some information included in the Recordset (such as for eample Datatype) seems to get lost. This results in dates being interpreted as strings by Excel.

Instead of using the Excel Transpose function I guess youre meant to use a loop, as that avoids the loss of information described above.

answered Apr 24, 2013 at 15:04

John Smizz

1931 gold badge3 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gembird · Accepted Answer · 2013-04-17 17:53:36Z

The type of field in ado recodset can't be changed but have you tried UDT? Here a short sample with user defined type which will wrap the ado recordset. Then in your code you will use your own recordset which will use Date data type.

Add reference to Microsoft ActiveX Data Objects Library.

Standard module vba code:

Private Const CONNECTION_STRING As String = "Provider=sqloledb;Data Source=SQLSERVER2012;Initial Catalog=Test;Integrated Security=SSPI;"
Private Const SQL_QUERY As String = "select * from dbo.DateTest"

Public Type MyRecord
    Id As Long
    CreatedAt As Date
End Type

Public Sub test()
    Dim myRecodset() As MyRecord
    myRecodset = GetSQL
End Sub

Public Function GetSQL() As MyRecord()

    On Error GoTo aError

    Dim adodbConnection As ADODB.Connection
    Set adodbConnection = New ADODB.Connection
    With adodbConnection
        .ConnectionString = CONNECTION_STRING
        .CursorLocation = adUseServer
        .Open
    End With

    Dim newRecordset() As MyRecord
    Dim newRecord As MyRecord
    Dim rowIndex As Long
    Dim adodbRecordset As ADODB.Recordset

    Set adodbRecordset = New ADODB.Recordset
    With adodbRecordset
        .Open SQL_QUERY, adodbConnection, adOpenStatic
        .MoveFirst

        ReDim newRecordset(.RecordCount - 1)

        Do While Not .EOF
            newRecord.Id = .Fields("Id")
            newRecord.CreatedAt = .Fields("CreatedAt")
            newRecordset(rowIndex) = newRecord
            rowIndex = rowIndex + 1
            .MoveNext
        Loop

    End With

    GetSQL = newRecordset

aError:
    If (Err.Number <> 0) Then MsgBox Err.Description
    adodbRecordset.Close
    adodbConnection.Close
End Function

Tested with this table:

CREATE TABLE [dbo].[DateTest]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CreatedAt] [date] NOT NULL
)

Hey So I have just figured out that using Worksheetfunction.Transpose actually CHANGES the datatype in VBA for Dates to Strings. I find this super surprising, but that is basically the reason as to why this error occurs. If you transpose the array by loop the type paramater gets maintained as vbDate and everything works smoothly. Surprising or am I being stupid?

Collectives™ on Stack Overflow

Issue with importing SQL Server date datatypes into Excel date formats

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related