0

I have a simple question on date column in oracle

Suppose I have a table called orders and few fields in it like orderid, orderdate, orderno etc..

I want the info like orderno, no of orders made on a particular day. To test this, I have created few orders on my test application. I have written the below query

SELECT orderid,orderno FROM orders WHERE orderdate='29-SEP-16';

the date format I mentioned in the query is correct. The above query returns nothing. If I change the where condition to >'28-SEP-16' it works.

Why my 1st query doesn't work?

CDspace
  • 2,639
  • 18
  • 30
  • 36
  • are the times stored with the dates? If so, then you won't get an equality match unless the correct time is provided, because Sept 29 @ 2pm is not equal to Sept 29 @ 3:15pm. If this is the case, then you can use date parts to compare only the day, month, and year (and not time). – Shannon Holsinger Oct 03 '16 at 18:56
  • It sounds like you are storing dates as text? – Ňɏssa Pøngjǣrdenlarp Oct 03 '16 at 18:58
  • The column has been created as type date and I'm not storing the time any where. The format is 'DD-MON-YY' –  Oct 03 '16 at 19:01
  • Maybe you should use `TO_DATE` function: `orderdate=TO_DATE('29-SEP-16')`. – Paviel Kraskoŭski Oct 03 '16 at 19:03
  • I tried, still returns null. –  Oct 03 '16 at 19:07
  • Please edit your post to show the query and how you execute it – Ňɏssa Pøngjǣrdenlarp Oct 03 '16 at 19:10
  • Try this condition: `>28-SEP-16 AND <30-SEP-16`. What will it display? – Paviel Kraskoŭski Oct 03 '16 at 19:10
  • [Oracle `Date` columns have a time component](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i1847). How did you insert your date data? Most likely the issue is the time portion of the field. – stephen.vakil Oct 03 '16 at 19:13
  • I tried between and it works!! But I want to know why does orderdate='29-SEP-16' doesn't work? –  Oct 03 '16 at 19:14
  • because there is *always* a time portion. it is pretty easy to make that part zero when you insert – Ňɏssa Pøngjǣrdenlarp Oct 03 '16 at 19:14
  • Maybe this will work: `TO_DATE(orderdate)=TO_DATE('29-SEP-2016')`. – Paviel Kraskoŭski Oct 03 '16 at 19:16
  • If orderdate is a date field, then it should be checked against a date explicitly rather than relying on implicit conversion from string to date. If it's not a date field... why not? If you wish to do various comparisons, especially before/after, then it should be. http://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html – ManoDestra Oct 03 '16 at 19:28
  • It is a date field and I'm checking it with the value as it is, stored in the database. When I do, select orderdate from orders the orderdate displays as 29-SEP-16, but when you do where orderdate='29-SEP-16' it doesn't work. I also tried altering the session to 'DD-MON-YY' –  Oct 03 '16 at 20:51

1 Answers1

1

A date column does not have a format (well, a date does have a particular packed binary representation that is very much non human readable). Oracle will attempt to implicitly cast a string to a date using your session's nls_date_format and will use the session's nls_date_format to display a string representation of a date (assuming your client application does not override these). You should not rely on implicit data type conversion, however. You should really use date literals or use to_date to do an explicit cast.

A date column always includes a time. Your nls_date_format may or may not include a time component so the time component may or may not be displayed. But it is always there.

Assuming your nls_date_format is dd-mon-rr, the query

SELECT orderid,orderno 
  FROM orders 
 WHERE orderdate='29-SEP-16';

will show you all orders where orderdate is Sept 29, 2016 at midnight. It will not show rows where the time component is anything after midnight. You can fix that by doing an inequality comparison. Using a date literal, that would be

SELECT orderid,orderno 
  FROM orders 
 WHERE orderdate >= date '2016-09-29';

or

SELECT orderid,orderno 
  FROM orders 
 WHERE orderdate >= date '2016-09-29'
   AND orderdate <  date '2016-09-30';

if you want to specify a range. Alternately, you could truncate the time portion of orderdate and do an equality comparison. I'll show the use of to_date for explicit conversions here

SELECT orderid,orderno 
  FROM orders 
 WHERE trunc(orderdate) = to_date( '29-SEP-16', 'DD-MON-RR' )

If you do this, however, you are likely to need a function-based index on trunc(orderdate).

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you so much!! It makes more sense now. I tried the query and it worked! –  Oct 03 '16 at 22:44