CODEWITHSUNDEEP
sqloracle-database
user1100941
user1100941

Reputation: 101

Select data between two dates where a date field falls between the two dates

I've the following query and I encounter the error : not a valid month. Help please!

SELECT * FROM TABLE1
WHERE Field1 = '12345' AND
      TO_DATE('4/28/2014 12:00:00 AM') BETWEEN TO_DATE(DATE1) AND TO_DATE(DATE2);

Thanks

Upvotes: 0

Views: 873

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You need to specify the format mask for the date conversion. It looks like your NLS_DATE_FORMAT is maybe DD/MM/YYYY, with or without a time part. You're passing the date in a different format. You should never rely on the NLS settings really, supply the format mask for TO_DATE() in the query:

SELECT *
FROM TABLE1
WHERE Field1 = '12345'
AND TO_DATE('4/28/2014 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
  BETWEEN DATE1 AND DATE2;

I'm assuming that DATE1 and DATE2 are already of type DATE, in which case there is no need to call TO_DATE; all that will do is convert them to and back from a string using the NLS_DATE_FORMAT, which is either pointless or will do the same as TRUNC(), depending on how that is set.

Of course, the date you're using here is at midnight, so specifying the time isn't adding anything; if you will never have a tie part then you could do either:

AND TO_DATE('4/28/2014', 'MM/DD/YYYY') BETWEEN DATE1 AND DATE2;

or

AND DATE '2014-04-28' BETWEEN DATE1 AND DATE2;

If field1 is numeric then you shouldn't have quotes around 12345 either, you're just adding an implicit TO_NUMBER().

But if you're storing numbers and/or dates as strings, you should really think again. Hopefully that is not in fact what you're doing...

Upvotes: 4

Evan Frisch
Evan Frisch

Reputation: 1374

Months for TO_DATE need to be two digits so you would need:

SELECT * FROM TABLE1 WHERE Field1 = '12345' AND TO_DATE('04/28/2014 12:00:00 AM') BETWEEN     TO_DATE(DATE1) AND TO_DATE(DATE2);

Reference http://www.techonthenet.com/oracle/functions/to_date.php for more info if you haven't already. Having a format mask could help define the string in case changes are made in the future.

Upvotes: 0

Related Questions