How do you use the where clause with date

I'm new at SQL so I need a little help with being able to pick dates.
I have a table that I'm running a select query against
SELECT arcusto_id, invoice_date
FROM arinvoice
WHERE arcusto_id = 25434
The result are something like this
25434         9/4/2014
25434         9/11/2014
25434         9/17/2014
Now I want to run the query by certain date
SELECT arcusto_id, invoice_date
FROM arinvoice
WHERE arcusto_id = 25434 AND invoice_date = '9/11/2014'
This is the result I would expect
25434        9/11/2014
But I get nothing but an  error telling me the month is not valid. I also tried 09 instead of 9.
What am i doing wrong
dkma2010Asked:
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.

Haris DulicCommented:
Hello,

to query dates from oracle you need to use  TO_DATE function so you query should be like

SELECT arcusto_id, invoice_date
FROM arinvoice
WHERE arcusto_id = 25434 AND invoice_date = to_date('9/11/2014','mm/dd/yyyy')

Open in new window

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
slightwv (䄆 Netminder) Commented:
To clarify the above post:
ALWAYS get in the habit of explicit data conversions.  Use TO_CHAR when querying a date column to ensure the result is ALWAYS exactly what you want.  Use TO_DATE when querying to make sure you get what you want.

Also make sure your invoice_date column doesn't have time portions.  Dates in Oracle have a time built in.  If the app developers didn't account for them, the column values may have them by 'accident'.

If they have times, the above query won't work.  You'll need to use '>=' and '<'.

To confirm this:
SELECT arcusto_id, to_char(invoice_date,'MM/DD/YYYY HH24:MI:SS')
FROM arinvoice
WHERE arcusto_id = 25434


If all the time portions are zeros, the above query should work fine.
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.