Date data from ADODB SQL returned to Excel as text, not date

peejay

Board Regular
Joined
Jul 11, 2003
Messages
79
I have written a complicated SQL script which runs fine, except for some reason a date column is being returned to Excel as text, rather than as a date.

The field is a date, and I've even tried CASTing the field to date in the SQL just in case, but still get the same problem when the recordset data is returned to Excel.
The column the Date data is returned to is formatted as 'MMM YYYY'.

I can run the SQL in MS SQL Server Management Studio, and copy/paste the results to the Excel sheet and the date copies as a date, but when I have this SQL run via Excel & ADODB, the date ends up as text (eg the text string 2015-01-31, instead of Jan 2015 with the date format).

I'm sure I've been able to run other queries returning date fields without this problem before, so I'm at a loss as to why it is happening now, and how to correct it.

Has anyone else encountered this, and solved it? I'd be very keen to hear how you fixed it.

Thx PJ
 

peejay

Board Regular
Joined
Jul 11, 2003
Messages
79
HI Kyle

My SQL is about 400 lines long, is quite complicated and not an issue so I won't post it to save causing confusion.

My VBA - which runs fine, apart from returning the date values as text - is as follows.

The table column the data is pasted into is formatted as 'MMM YYYY', and all preceding columns are formatted as GENERAL.


Option Explicit

'Module wide variables
Dim shtVar As Worksheet 'pointer to worksheet storing variables
Dim shtOut As Worksheet 'pointer to the results worksheet
Dim tblOut As ListObject 'pointer to the results table (list object)
Dim rngOut As Range 'pointer to the results range
Dim strCxn As String 'connection string for the query
Dim strSQL As String 'the query's SQL text
Dim strStatus As String 'Status Bar message
Dim booStatus As Boolean 'current status bar state
Dim dteStart As Date 'time query started


'==============================================================================
Sub Run_a_data_query()
'Assumes connection string to be used is stored in the workbook as a range name
'==============================================================================
If MsgBox("Do you want to run the 'Update Balance Sheet data' query ?", _
vbYesNo, _
"RUN QUERY ? ...") <> vbYes Then
Exit Sub

Else 'run the query

'setup the Excel working environment
Call SetupSubEnvironment

'set up variables & objects
'==========================
dteStart = Now 'store start time

Set shtVar = Worksheets("Inputs_Constants")
Set shtOut = Worksheets("SQL data")
Set tblOut = shtOut.ListObjects("tblActualsData")
Set rngOut = Range(tblOut).Cells(1, 1)

'store the SQL code, connection string & status bar text
'NOTE: The SQL is split over 2 cells as I cannot paste all of it into one cell
strSQL = shtVar.Range("qryData_1").Value & shtVar.Range("qryData_2").Value
strCxn = shtVar.Range("cxnData").Value
strStatus = "Running query 'Update Balance Sheet data'; please wait..."

'clear any active table filters
Call Clear_Table_Filters

'delete any existing records from the table
Call Delete_Table_Body

'run the query
Call Run_Query

'then reset the Excel working environment
Call ResetSubEnvironment

Beep 'to tell the user the procedure has completed

shtOut.Activate

MsgBox "The query has successfully completed, and took " & _
Format(Now - dteStart, "h\h:mm\m:ss\s") & " to run.", _
vbInformation + vbOKOnly, _
"QUERY COMPLETE..."
End If

End Sub '**********************************************************************


'==============================================================================
Sub Run_Query()
'==============================================================================

On Error Resume Next 'turn on error trap

'Store Current Query to Check For Errors
Dim objCxn As ADODB.Connection
Dim objRecSet As ADODB.Recordset

If Err.Number <> 0 Then
MsgBox "The ActiveX Data Objects library isn't connected to this file." & _
vbCr & vbCr & _
"In the VBA Editor, select the Tools | References options, " & _
"select the Microsoft ActiveX Data Objects library & try again.", _
vbOKOnly + vbCritical, _
"NO CONNECTION TO ActiveX Data Objects Library..."
'turn off error trap
On Error GoTo 0
Exit Sub
End If

'continue here if ADODB variables created...
Application.Calculation = xlCalculationManual

'Create the Connection and Recordset objects
Set objCxn = New ADODB.Connection
Set objRecSet = New ADODB.Recordset

' Open the connection and execute; On Error trap should still be active...
With objCxn
.CommandTimeout = 0
.Open strCxn
End With

If Err.Number <> 0 Then
MsgBox "The ActiveX Data Objects connection could not be opened." & _
vbCr & vbCr & _
"In the VBA Editor, select the Tools | References options, " & _
"select the Microsoft ActiveX Data Objects library & try again.", _
vbOKOnly + vbCritical, _
"ADODB CONNECTION COULD NOT BE OPENED..."
Application.Calculation = xlCalculationAutomatic
'turn off error trap
On Error GoTo 0
Exit Sub
End If

'Continue here if connection was able to be opened.
'Turn off error trap, as there should be no more trappable errors
On Error GoTo 0

Set objRecSet = objCxn.Execute(strSQL)

'Check we have data...
If Not objRecSet.EOF Then
'then store the result to the output range, ...
rngOut.CopyFromRecordset objRecSet
'and close the record set
objRecSet.Close
Else
MsgBox "The query returnd NO records.", vbCritical, "ERROR..."
End If

'release the query's object pointers
Set objCxn = Nothing
Set objRecSet = Nothing

Application.Calculation = xlCalculationAutomatic

End Sub '**********************************************************************


'==============================================================================
Sub SetupSubEnvironment()
'==============================================================================

With Application
'Set interface options
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False

'setup Status Bar
booStatus = Application.DisplayStatusBar
.DisplayStatusBar = True

'Set Mouse Cursor to Wait
.Cursor = xlWait
End With

End Sub '**********************************************************************


'==============================================================================
Sub ResetSubEnvironment()
'==============================================================================

With Application
'Reset Mouse Cursor
Application.Cursor = xlDefault

'Reinstate original StatusBar
.StatusBar = False
.DisplayStatusBar = booStatus

'Reset interface options
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True

End With

Set shtVar = Nothing

End Sub '**********************************************************************


'==============================================================================
Sub Delete_Table_Body()
'If data rows exist on table tblOut, delete them (keeping any table formulas)
'==============================================================================
With tblOut
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
End Sub '**********************************************************************


'==============================================================================
Sub Clear_Table_Filters()
'If a filter on sheet shtOut's table tblOut exists, clear it
'==============================================================================
If shtOut.FilterMode = True Then
tblOut.Range.AutoFilter
End If
End Sub '**********************************************************************


'==============================================================================
Sub Clear_Sheet_Filters(sht As Worksheet)
'clear any Auto Filters on sheet 'sht'
'==============================================================================
With sht
On Error Resume Next
.ShowAllData
On Error GoTo 0
End With
End Sub '**********************************************************************
 

peejay

Board Regular
Joined
Jul 11, 2003
Messages
79
UPDATE

I think I've nowSOLVED this myself.

In my SQL, I was using the DATE type for my date field, rather than DATETIME. After setting any date constants and new date fields as type DATETIME then re-running my SQL in VBA, my data was returned as a date field in Excel.

Note - I'm not sure whether this influenced the outcome when I used DATETIME, but my Excel cell format for the column was 'MMM YYYY' at the import time (however this made no difference when using the DATE type in the SQL, so I doubt it made a difference).

This is probably another Microsoft 'undocumented feature' 8^P
 

Forum statistics

Threads
1,082,369
Messages
5,365,049
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top