Rx_
Nothing In Moderation
- Local time
- Today, 06: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.
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.
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
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
Last edited: