-1

In my table sales there is expiry date field which is of date type. Now, I have to write query which selects all records having expiry date greater than current system date.

select * from Sales where expiry_date > sysdate;

in output I am getting all records with expiry date 31/12/9999 00:00:00, which is not desired.

T.S.
  • 18,195
  • 11
  • 58
  • 78
Ankur Goel
  • 5
  • 1
  • Please give some example data and what you expect as output – bish Jul 17 '15 at 03:51
  • So is that mysql or oracle? – alariva Jul 17 '15 at 03:53
  • @Alariva it's oracle – Ankur Goel Jul 17 '15 at 03:55
  • @bish there are different expiry dates in sales i want to print records having expiry date greater than today's date. – Ankur Goel Jul 17 '15 at 03:57
  • @bish like i have two records having expiry date 11-12-2014 & 12-12-2015 then i wish to print second one only. – Ankur Goel Jul 17 '15 at 03:59
  • possible duplicate of [oracle sql date not later than today](http://stackoverflow.com/questions/12125401/oracle-sql-date-not-later-than-today) – alariva Jul 17 '15 at 04:02
  • Your inquiry can be addressed by reading this analogous answer http://stackoverflow.com/questions/12125401/oracle-sql-date-not-later-than-today – alariva Jul 17 '15 at 04:03
  • Your query does what you want. Your data is incorrect - there's nothing we can do about that. Either remove the magic values (Check if you're breaking something first) or add an additional condition to your query to remove them. – Ben Jul 17 '15 at 05:18

2 Answers2

3

" in output i am getting all records with expiry date 31/12/9999 00:00:00 which is not desired."

But that's what your query asks for: you must admit that the year 9999 is greater than the current year.

So either your query is correct and you have misunderstood the requirement, or you need to re-write the query to explicitly exclude records with the maximum date.

Presumably in this case EXPIRY_DATE was defined as NOT NULL and it was too late to change it when somebody raised the matter of records which never expire. So instead we have a magic value of 31-12-9999 is ,which means of "these records do not expire".

Anyhow, here is the query now:

select * from Sales 
where expiry_date > sysdate
and expiry_date != date '9999-12-31';

This is a common problem with magic values: they offer a quick fix for an architectural problem but levy an ongoing tax on application logic.

APC
  • 144,005
  • 19
  • 170
  • 281
  • when i am using Select expiry_date from sales; I am getting exact dates which are saved in table. but When i am using above query mentioned by me ,in output I am getting all expiry dates as 31/12/9999 00:00:00. – Ankur Goel Jul 17 '15 at 08:38
  • Issues like that, you're going to have to post some sample data and actual queries. – APC Jul 17 '15 at 22:28
0

Ahh the end of time, a date so far in the future the system storing it is unlikely to ever encounter it for real. The problem with that is that systems have a nasty habit of lasting longer than expected in one form or another.

Be that as it may, it looks like you want to exclude the end of time since the assumption is that those records don't truly expire, but gosh darn it you needed some kind of date in there for that index to work the way you wanted it to.

So you either need to directly exclude records where the expiration date equals your end of time value, or use a range condition that does the same:

where expiry_date between sysdate and date '9999-12-30 23:59:59'
Sentinel
  • 6,379
  • 1
  • 18
  • 23
  • `between` is wrong, because its lower bound is `>=`; the OP's query specifies only greater than. – APC Jul 17 '15 at 06:21