Last post Sep 25, 2012 08:17 AM by fimine
Sep 24, 2012 11:38 AM|ying87|LINK
Do anyone know how to use IN operator for date values?
I have 1 query as below, which I can put any date value to retrieve the data but when I execute I get ORA-01843: not a valid month.
where time_stamp IN ('02/28/2012', '07/22/2012', '09/29/2012')
Num Qty Time_stamp
1 2 02/28/2012
2 2 07/22/2012
3 2 09/29/2012
Any idea how to solve this? Instead of using IN operator, is that others way?
Sep 24, 2012 03:04 PM|AZMatt|LINK
I don't have an Oracle environment to test this in, but you should be able to convert the time_stamp field to a date-formatted varchar...
where Convert(time_stamp, varchar(10), 101) IN ('02/28/2012', '07/22/2012', '09/29/2012')
Sep 24, 2012 03:05 PM|AZMatt|LINK
Sep 24, 2012 03:43 PM|RichardY|LINK
AZMatt's reply looks like it will work (although I am lost without PL/Sql Developer which I do not have access to now), but I would normally prefer to convert from the string to the date object for comparison.
You might try building your IN clause using the to_date() function. Just supply a format like:
to_date('02/28/2012' , 'MM/DD/YYYY')
Sep 25, 2012 04:29 AM|ying87|LINK
Thanks for reply my question.
I have try like below, but still get ORA-00907: missing right parenthesis.
where time_stamp IN ('to_date('02/28/2012', 'mm/dd/yyyy')')
I also did try on the statment provided by AZMatt but still get the error.
Where I did wrong? Can you please show me the correct one?
Sep 25, 2012 07:43 AM|RichardY|LINK
You should not surround the to_date() function with single quotes.
where time_stamp in
Sep 25, 2012 08:17 AM|fimine|LINK
If you`re using Oracle sql developer check your NLS settings (Tools->Preferences->Database-NLS) - there you'll have format settings for both timestamp and date. It seems, that there is a mismatch with you're select queries.
Basically, the DB thinks that your 28 is a month and 02 is day. Either change NLS or the query.