CODEWITHSUNDEEP
sqloracle-database
Rolando
Rolando

Reputation: 62596

How to query date in oracle?

Assuming I have the following table in oracle:

id|orderdatetime (date type)|foodtype (string type)
1|2013-12-02T00:26:00 | burger
2|2013-12-02T00:20:00 | fries

... (assume there are many dates and times)

Assuming someone happened to have a date in mind (i.e. "2010-12-02T00:25:00"). even though there is no database entry with that specific time in there... is there some way to query the database such that I can get the row that has a date time that is closest to it without being ahead of the date in mind (ideally, it would be less than or equal to)?

(i.e. in this case, the sql query would return the row for "fries" and not "burger" because the time for burger is past the time the user had in mind despite the fact that the time for "burger" is closer.)

Upvotes: 0

Views: 340

Answers (1)

Brian
Brian

Reputation: 1387

select x.* from (select id,orderdatetime,foods from orders
where orderdatetime <= YOURTIME order by orderdatetime desc)x
where rownum =1

Another would be:

select * from orders where orderdatetime = (select max(orderdatetime) from orders
where orderdatetime <= YOURTIME)

Upvotes: 2

Related Questions