What is the Oracle code for converting the format of a date variable?
Hello,
I am trying convert my SQL code into its Oracle equivalent. One part of the original code involves taking the value in a cell, which is today's date, and converting it into the appropriate format within a variable.
Below is an example of the code.
With Selection.ListObject.QueryTable
.Connection = Conn
.CommandText = Array(comstring)
.CommandText = .CommandText + " WHERE (TRAD In ('LSWL'))"
.CommandText = .CommandText + " AND (INPUTDATE={ts '" & opicsdate & "'})"
.CommandText = .CommandText + " AND (DETAILIND not In ('FXTR'))"
.Refresh BackgroundQuery:=False
End With
If I wish to convert into Oracle then I have been informed I would need to change the date line as shown below. I would need to manually type in the relevant date.
.CommandText = .CommandText + " AND (INPUTDATE=to_date('2015/09/03 00:00:00','YYYY/MM/DD HH24:MI:SS'))"
What I would like to do is still be able to keep that opicsdate variable. Is that possible?
comstring = "SELECT BR, TRAD, DEALNO, VDATE, CCY, CTRCCY, CCYAMT, CTRAMT, DETAILIND FROM " & db
With Selection.ListObject.QueryTable
.Connection = Conn
.CommandText = Array(comstring)
.CommandText = .CommandText + " WHERE (TRAD In ('LSWL'))"
.CommandText = .CommandText + " AND (INPUTDATE={ts '" & opicsdate & "'})"
.CommandText = .CommandText + " AND (DETAILIND not In ('FXTR'))"
.Refresh BackgroundQuery:=False
End With
>>DRIVER=SQL Server;SERVER=WPPWD01V0241OPICSREPLPROD,11000;UID=a627040;APP=Microsoft Data Access Components
to_date is an Oracle thing. You asked this in an Oracle Topic Area.
I see nothing Oracle in your connect string...
Is this question related to Oracle?
>> I am not completely sure what you mean. Are you suggesting that I would need to type out the code in full?
The bind variable part?
There are MANY links out there that talk about bind variables and SQL Injection.
You set up a generate SQL call with a place holder for the substitution. You then create a parameter for the command object.
If you cannot find anything:
What you posted looks like either VBScript or .Net. Confirm which one it is and I'll try to find a relevant link to explain bind variables.
f19l
ASKER
Currently I have an excel spreadsheet that connects via ODBC to a number of database tables. The spreadsheet uses the VB coding that will allow those links to be refreshed every time I run the file. I am now engaged in testing a new Oracle database that will require me to amend the queries.
To further add to johnsone's post above: In Oracle dates have a time portion built in. You need to account for that if the application that does the inserts populates the time portion.
Connect to the database using sqplpus or SQL Developer and take a look:
select to_char(INPUTDATE,'MM/DD/YYYY HH24:MI:SS') from OPICS_CUSTOM.POSTBATCH_FXTW POSTBATCH_FXTW where rownum<101;
If you see data in the time portion, you'll need to change your query.
I like:
and INPUTDATE >= to_date('01/01/2001') and INPUTDATE< to_date('01/01/2001')+1
Of course converting '01/01/2001' to bind variables (from the link I provided, be sure to repeat the parameters).
Below is my code. would I need to type it all out?
Conn = "ODBC;Description=Opics Prod Replication;DRIVER=SQL Server;SERVER=WPPWD01V0241
opicsdate = Format(Range("Date"), "yyyy-mm-dd hh:mm:ss")
db = "OPICS_CUSTOM.POSTBATCH_FX
comstring = "SELECT BR, TRAD, DEALNO, VDATE, CCY, CTRCCY, CCYAMT, CTRAMT, DETAILIND FROM " & db
With Selection.ListObject.Query
.Connection = Conn
.CommandText = Array(comstring)
.CommandText = .CommandText + " WHERE (TRAD In ('LSWL'))"
.CommandText = .CommandText + " AND (INPUTDATE={ts '" & opicsdate & "'})"
.CommandText = .CommandText + " AND (DETAILIND not In ('FXTR'))"
.Refresh BackgroundQuery:=False
End With
Thanks.