asked on
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.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
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/0
What I would like to do is still be able to keep that opicsdate variable. Is that possible?
Thanks.
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.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
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/0
What I would like to do is still be able to keep that opicsdate variable. Is that possible?
Thanks.
Oracle DatabaseVBA
Last Comment
f19l
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>>DRIVER=SQL Server;SERVER=WPPWD01V0241
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.
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.
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.
First I'll apologize for all the unnecessary posts in the thread but it does seem to have a working example of bind variables from VBA:
https://www.experts-exchange.com/questions/24584127/Bind-Variables-via-ADO.html
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/Y
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).
https://www.experts-exchange.com/questions/24584127/Bind-Variables-via-ADO.html
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/Y
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).
ASKER
Thanks. That is all useful to have.
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.