Data Mining - Convert Oracle / SQL Text Dates to real dates types to move into Excel (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 10:12
Joined
Oct 22, 2009
Messages
2,803
How could this be accomplished in TSQL code?
Came across an interesting conversion process and found a solution.
Would be opent to alternative solutions if anyone cares to contribute.

The SQL Server 2008 has a Linked Server to Oracle. A Date field in Oracle is actually text. It looks like this: 6/12/2009 6:06:23 AM
Moving this format to Excel was of data type text. So, it wouldn't sort as a date data type.
See attachment for final *desired* output for Excel.

In the Query, first used the Format(Date_Created, 'short date')
This did format the output to appear as the short date. However, on the Excel side; however, it was still only text. It didn't group dates as shown on the attachment. The Excel filtered list simply had a huge list of each date in the sort and filter because it was still just text fields.
Formatting this text column from Excel to Date/Time did no good either.

Using the
cvdate(Format(Date_Created, 'short date')) as [Date Created]
was the solution.
It appeared the same as the short-date format, but it was actually a Date/time data type.

The question is: Is there a equal way to do this in the SQL Server View that consumes the Oracle text date value?
The Query is based off a view based on a table of an Oracle Linked Server. So, the SQL Server View would need the equalivant conversion of a text to date.

Code:
Function SQLTextForAutomation(ID_Area As String) As String
' Added update_TS to view Nav_Well_Den on 8/6/2014 - they may use these dates to designate Historical for matching.
    StrSQL = ""
    StrSQL = StrSQL & "SELECT vNV_Well_DEN_View.WELL_NAME AS [Well Name],  " & _
                       cvdate(Format(Date_Created, 'short date')) as [Date Created] "
        StrSQL = StrSQL & "ORDER BY vNV_Well_DEN_View.WELL_NAME;"
    SQLTextForAutomation= StrSQL
    'Debug.Print StrSQL
End Function
 
'  Then later in code
    intRowPos = 1
    objXL.Worksheets(intWorksheetNum).Name = "MyWorksheet"
        Set rsDataNav_NavNoMatchReg = CurrentDb.OpenRecordset(SQLTextForAutomation, dbOpenSnapshot, dbReadOnly + dbSeeChanges) ' create recordset based on SQL statement
    intRowPos = 6      ' Sets starting Row for data in Excel - reference fields to this
 
    objXL.DisplayAlerts = False                                                       ' Turn off Display Alerts
    objXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsDataNav_NavNoMatchReg    
    intMaxRecordCount = rsDataNav_NavNoMatchReg.RecordCount - 1            ' - use for max rows returned in formatting later
Important Update!
IIf(Not IsNull([Date_Created]),CVDate(Format([Date_Created],'Short Date')),Null) AS [Date Created]
This fixed another unexpeced problem
Leaving out the IIf statement to check for nulls.... the query returned records to Excel until it hit a null.
The Same query in Access Query Design returned all the records.
In other words, running the SQL string in and Acess Query returned 2,000+ records
In code, the rsDataNav_NavNoMatchReg.recordcount returns the same 2,000+ records
But the
But, in the objXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsDataNav_NavNoMatchReg
only returned 256 records - that was the exact location of a missing date!

By checking for null and replaceint it with a vba Null in the IIf statement, the same code returned 2,000+ records to Excel.
Just be aware that the CopyFromRecordset has an interesting quirk.
 

Attachments

  • 8-9-2014 Dates Converted for Excel.png
    8-9-2014 Dates Converted for Excel.png
    19.6 KB · Views: 155
Last edited:

Users who are viewing this thread

Top Bottom