Avatar of f19l
f19l
 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.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?

Thanks.
Oracle DatabaseVBA

Avatar of undefined
Last Comment
f19l

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
f19l

ASKER
Regarding the first part of your reply, I am not completely sure what you mean. Are you suggesting that I would need to type out the code in full?

Below is my code. would I need to type it all out?

Conn = "ODBC;Description=Opics Prod Replication;DRIVER=SQL Server;SERVER=WPPWD01V0241\OPICSREPLPROD,11000;UID=a627040;APP=Microsoft Data Access Components;WSID=DTC038137BD0E4E;Trusted_Connection=Yes"

opicsdate = Format(Range("Date"), "yyyy-mm-dd hh:mm:ss")

db = "OPICS_CUSTOM.POSTBATCH_FXTW POSTBATCH_FXTW"

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

Thanks.
SOLUTION
johnsone

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

>>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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
slightwv (䄆 Netminder)

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/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).
f19l

ASKER
Thanks. That is all useful to have.