Проблема с импортом типов данных SQL Server в форматы даты Excel

Question

Я импортирую данные в Excel из базы данных SQL Server 2012. У меня проблема в том, что SQL Server 2012 Date столбцы типа данных не распознаются последовательно в Excel.

Если я использую наборы записей ADO для импорта данных из определенных столбцов, данные с типом данных столбца Date или же Datetime копируется в набор записей как тип данных VBA String (вместо Date или же Integer).

Воспроизведение набора записей в Excel дает строки в формате yyyy-mm-dd, Эти ячейки не распознаются Excel как дата / время, даже если я изменяю форматирование.

Тем не менее, когда я ссылаюсь на ячейку Excel из другой ячейки Date тип распознан (например: A1 содержит результат моего запроса набора записей SQL VBA, например, "2013-04-17". Я ввожу в ячейку A2 "=A1 + 1", A2 отобразит его как 41382.

Однако, когда я использую MS Query для извлечения данных из той же базы данных, используя ListObjects и ODBC, те же данные из той же базы данных возвращаются с датами, правильно интерпретируемыми Excel (т.е. я импортирую столбец с именем "Дата транзакции" с типом данных SQL Datetime через MS Query результатом будет дата MS Excel "Integer").

Как изменить код набора записей VBA для обработки данных в формате yyyy-mm-dd как даты вместо строк?

Спасибо Джон

PS: SQL-запросы, которые я выполняю, различаются, поэтому иногда я получаю данные из столбцов с типом данных "date", а иногда просто с целыми числами или типом данных Double. Это означает, что жесткая запись "Convert(INT, TransactionDate)" в оператор SELECT на самом деле невозможна. Тем не менее, я абсолютный любитель SQL, поэтому может быть очень простое решение (например, если я когда-нибудь получу доступ к столбцам с типом данных "DateTime", тогда SQL всегда должен отправлять "CONVERT(DBL, XXX)" вместо XXX, где XXX - это столбец с типом данных 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

Answer 1

Проблема возникла из-за использования функции Excel Transpose вместо циклического просмотра содержимого объекта результатов Recordset.

В процессе транспонирования через Excel некоторая информация, включенная в набор записей (например, для типового образца), кажется, теряется. Это приводит к тому, что даты интерпретируются Excel как строки.

Вместо того, чтобы использовать функцию транспонирования Excel, я предполагаю, что вы должны использовать цикл, чтобы избежать потери информации, описанной выше.

Answer 2

Тип поля в ado recodset не может быть изменен, но вы пробовали UDT? Вот краткий пример с определенным пользователем типом, который обернет набор записей ado. Затем в своем коде вы будете использовать свой собственный набор записей, который будет использовать тип данных Date.

Добавить ссылку на библиотеку объектов данных Microsoft ActiveX.

Стандартный модуль VBA код:

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

Протестировано с этой таблицей:

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