L
Legacy 234512
Guest
hello,
how can i convert data from a database stored as DATE type into a spreadsheet.
at the moment, recordset is returning date in format like 13131360
and i would love it to be dd/mm/yyyy
I have tried CONVERT(VARCHAR(10), CURDATE, 103) but that doesnt seem to do anything
Do I have to iterate through the recordset and convert each row separately?
Any help is appreciated
how can i convert data from a database stored as DATE type into a spreadsheet.
at the moment, recordset is returning date in format like 13131360
and i would love it to be dd/mm/yyyy
I have tried CONVERT(VARCHAR(10), CURDATE, 103) but that doesnt seem to do anything
Do I have to iterate through the recordset and convert each row separately?
Any help is appreciated
Code:
Sub dbConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range
Dim rst As ADODB.Recordset
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)
With wsSheet
Set rnStart = .Range("A2")
End With
stSQL = "SELECT CURDATE, ORDNAME FROM PORDERS"
With cn
.CursorLocation = adUseClient
.Open "Driver={SQL Server};Server=serverpath; DAtabase=maindb; UID=user; PWD=pass"
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
Dim i As Long
With rst
For i = 1 To .Fields.Count
wsSheet.Cells(1, i) = .Fields(i - 1).Name
Next i
End With
' add recordset starting A2
rnStart.CopyFromRecordset rst
cn.Close
End Sub
Last edited by a moderator: