Lavanya Pandramish Lavanya Pandramish - 3 months ago 19
C# Question

Date column in oracle

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
it works.

Why my 1st query doesn't work?


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';


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).