(recordset ) Date conversion from sql to excel

[Legacy 234512]

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

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

 

[Fazza]

hi

Please, can you explain how 13131360 converts to a date? It is not familiar or obvious to me.

If it is a simple algorithm it might be do-able in the SQL. Such as,

SELECT function/s_to_convert(CURDATE) AS [CURDATE], ORDNAME FROM PORDERS

Basic functions can be used in the SQL. regards

 

[Legacy 234512]

13240800 = 05/03/13 this is how its displayed in the DBMS

the dbms intereface does have an option to create excel reports - generated sql query looks like this
( when i run generated reports dates are correctly displayed in excel ) - in format dd/mm/yyyy

select gsmdata.dbo.AFORM.CURDATE ,
gsmdata.dbo.SERIAL.SERIALNAME ,
gsmdata.dbo.PART.PARTNAME ,
gsmdata.dbo.PART.PARTDES ,
(0.0 + ( (0.0 + ( (0.0 + ( convert(float, gsmdata.dbo.SERIAL.QUANT ) / 1000.000000 )) )) )) ,
(0.0 + ( (0.0 + ( sum( (0.0 + ( (0.0+ convert(float, convert( bigint , round( ( (0.0+ case when ( ( ( coalesce( gsmdata.dbo.ALINEA.SUMEMPASPAN , 0 ) + gsmdata.dbo.ALINE.EMPASPAN ) > 0 ) ) then ( case when ( ( system.dbo.DAYS.DAYNUM = 0 ) ) then ( gsmdata.dbo.ALINE.EMPASPAN ) else ( gsmdata.dbo.ALINEEMP.ASPAN ) end ) else ( 60 / (case when ( ( coalesce( gsmdata.dbo.ALINEA.NUMEMPLOYEES , 0 ) + 1.000000000 ) ) = 0 then 1 else ( ( coalesce( gsmdata.dbo.ALINEA.NUMEMPLOYEES , 0 ) + 1.000000000 ) ) end) ) end ) / (case when ( (0.0+ case when ( ( ( coalesce( gsmdata.dbo.ALINEA.SUMEMPASPAN , 0 ) + gsmdata.dbo.ALINE.EMPASPAN ) > 0 ) ) then ( ( coalesce( gsmdata.dbo.ALINEA.SUMEMPASPAN , 0 ) + gsmdata.dbo.ALINE.EMPASPAN ) ) else ( 60 ) end ) ) = 0 then 1 else ( (0.0+ case when ( ( ( coalesce( gsmdata.dbo.ALINEA.SUMEMPASPAN , 0 ) + gsmdata.dbo.ALINE.EMPASPAN ) > 0 ) ) then ( ( coalesce( gsmdata.dbo.ALINEA.SUMEMPASPAN , 0 ) + gsmdata.dbo.ALINE.EMPASPAN ) ) else ( 60 ) end ) ) end) * gsmdata.dbo.ALINE.QUANT ) ,0)) ) / 1000.000000 ) )) ) )) )) ,
gsmdata.dbo.WORKC.WORKCNAME ,
gsmdata.dbo.ACT.ACTNAME ,
gsmdata.dbo.ACT.ACTDES ,
gsmdata.dbo.PROCESS.PROCNAME ,
gsmdata.dbo.PROCESS.PROCDES

from system.dbo.ENVIRONMENT inner join gsmdata.dbo.PART on 1 = 1
inner join gsmdata.dbo.ALINEEMP on 1 = 1
inner join system.dbo.DAYS on ( system.dbo.DAYS.DAYNUM <= 1 ) and ( system.dbo.DAYS.DAYNUM >= 0 )
inner join gsmdata.dbo.ALINE on ( gsmdata.dbo.ALINEEMP.AL = case when ( ( system.dbo.DAYS.DAYNUM = 0 ) ) then ( 0 ) else ( gsmdata.dbo.ALINE.AL ) end ) and ( gsmdata.dbo.ALINE.PART = gsmdata.dbo.PART.PART ) and ( gsmdata.dbo.ALINE.CURDATE <= 0 ) and ( gsmdata.dbo.ALINE.CURDATE >= 0 ) and ( gsmdata.dbo.ALINE.FORM > 0 )
inner join gsmdata.dbo.SERIAL on ( gsmdata.dbo.SERIAL.SERIAL = gsmdata.dbo.ALINE.SERIAL )
inner join gsmdata.dbo.AFORM on ( gsmdata.dbo.AFORM.FORM = gsmdata.dbo.ALINE.FORM )
inner join gsmdata.dbo.WORKC on ( gsmdata.dbo.WORKC.WORKC = gsmdata.dbo.ALINE.WORKC )
inner join gsmdata.dbo.PROCESS on ( gsmdata.dbo.PROCESS.T$PROC = gsmdata.dbo.PART.T$PROC )
inner join gsmdata.dbo.ACT on ( gsmdata.dbo.ACT.ACT = gsmdata.dbo.ALINE.ACT )
inner join system.dbo.USERSB on ( system.dbo.USERSB.USERB = case when ( ( system.dbo.DAYS.DAYNUM = 0 ) ) then ( gsmdata.dbo.ALINE.SERN ) else ( gsmdata.dbo.ALINEEMP.USERB ) end )
inner join gsmdata.dbo.SHIFTS on ( gsmdata.dbo.SHIFTS.SHIFT = case when ( ( system.dbo.DAYS.DAYNUM = 0 ) ) then ( gsmdata.dbo.ALINE.SHIFT ) else ( gsmdata.dbo.ALINEEMP.SHIFT ) end )
left outer join gsmdata.dbo.SERACT on ( gsmdata.dbo.SERACT.SERIAL = gsmdata.dbo.ALINE.SERIAL ) and ( gsmdata.dbo.SERACT.ACT = gsmdata.dbo.ALINE.ACT )
left outer join gsmdata.dbo.PROCACT on ( gsmdata.dbo.PROCACT.T$PROC = gsmdata.dbo.PART.T$PROC ) and ( gsmdata.dbo.PROCACT.ACT = gsmdata.dbo.ALINE.ACT )
left outer join gsmdata.dbo.ALINEA on ( gsmdata.dbo.ALINEA.AL = gsmdata.dbo.ALINE.AL )

where ( system.dbo.ENVIRONMENT.DNAME = 'gsmdata' ) and ( 1 = 1 )
group by gsmdata.dbo.AFORM.CURDATE
, gsmdata.dbo.SERIAL.SERIALNAME
, gsmdata.dbo.PART.PARTNAME
, gsmdata.dbo.PART.PARTDES
, (0.0 + ( (0.0 + ( (0.0 + ( convert(float, gsmdata.dbo.SERIAL.QUANT ) / 1000.000000 )) )) ))
, gsmdata.dbo.WORKC.WORKCNAME
, gsmdata.dbo.ACT.ACTNAME
, gsmdata.dbo.ACT.ACTDES
, gsmdata.dbo.PROCESS.PROCNAME
, gsmdata.dbo.PROCESS.PROCDES having count(*) > 0
order by 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11

 

[Legacy 234512]

The formatting of each cell holding date after generating a report is *dd/mm/yyyy hope that helps

 

[Fazza]

It seems you want to convert a date from a database to Excel - yet it does that already?

I do not understand.

 

[Legacy 234512]

It seems you want to convert a date from a database to Excel

yes, exactly - but the database stores the date as BIGINT datatype so in excel it looks like 13240800 and thats what my problem is. i need to convert the number to an actual date that looks like dd/mm/yyyy

 

[Legacy 234512]

1 day within bigint value equals 1440
so bigint / 1440 gives the amount of days elapsed from starting point
starting point is 01/01/1988 ( ez calc )
therefore SELECT DATEADD(day,CURDATE/1440, '01/01/1988') gives me result i want!