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.
f19lAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
First:  Use bind variables.  Never concatenate strings in an Oracle command.

It opens you up to SQL Injection.

If you feel you must use string concatenation, something like:
.CommandText = .CommandText + " AND (INPUTDATE=to_date('" &  opicsdate  & " 00:00:00','YYYY/MM/DD HH24:MI:SS'))"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
f19lAuthor Commented:
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.
0
johnsoneSenior Oracle DBACommented:
Just to add.  If you don't have a time component, you don't need to put it into the TO_DATE.  00:00:00 (midnight) is the default.

i.e.

.CommandText = .CommandText + " AND (INPUTDATE=to_date('" &  opicsdate  & "','YYYY/MM/DD'))"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
slightwv (䄆 Netminder) Commented:
>>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.
0
f19lAuthor Commented:
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.
0
slightwv (䄆 Netminder) Commented:
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:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24584127.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).
0
f19lAuthor Commented:
Thanks. That is all useful to have.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.