• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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
0
dkma2010
Asked:
dkma2010
1 Solution
 
Haris DjulicCommented:
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now