SixSigns Blog

I get a lot of questions on how Oracle handles dates and how to limit the found records in a select. So let us jump right in to the topic.Limiting recordsMost people coming from another SQL database ask me how to limit the records in a select. With MySQL it s a simple “limit 10″ and your select returns only 10 records. With Oracle it is sort of the same but you get more power with your limitation.So for only returning 10 records we would create a select like:

select * from yourtablewhere rownumber < 11

This will return exactly 10 records from your table. Now it sometimes happens more then once that you will need to sort your records as well. Now the obvious would be to create the select like:

select * from yourtable
where rownumber < 11
orderby mydate

At first this looks all good, but if you look closer (and if you have a table with different dates in it) you will see that your select returns 10 records but they are not sorted by the correct date.

Let’s say that you need to sort by the first date in your table as well then your result will likely be wrong.To overcome this we need to create a subquery and then only select from those returned results the 10 records.

Ok, so we would create a select like:

select * from
    (
         select * from yourtable
         order by least(user_create_date)
    )
where rownum < 11

Here we go. Now we got the records we want. The inner select statement selects all records and orders them by the date (the least() tells Oracle to sort the records by the least date). Then in the outer select we “limit” the records found by 10.

Handling Dates

Oracle has the internal date format of “month-day-year”, thus your dates should like “07-13-2006″. Thus you can actually just use a select like:

select * from yourtablewhere mydate < ‘07-13-2006′

That is when you have a field of type “date”. Of course, this is a simple select, but it should give you a idea how to handle dates in Oracle.There are some build in functions that will let you work with dates more.

If you want to get the current date the just issue a select like:

select sysdate from dual

or get the current date with:

select current_date from dual

Another useful one is to get the system timestamp. With it you get the systems date in the “TimeStamp” format:

select systimestamp from dual

If you are not working with Oracle a lot then you are propably asking yourself; What the heck is “Dual”?

The dual table is a small, but very helpful, table that is available to you all the time within Oracle. The dual table only contains one field called “dummy” of the type “varchar(1)”.

Actually any select you issue with dual does not care about the type or alike. Thus the dual table is the perfect testing table before you use it in your real one.

I hope that I could help someone with the above.

If you enjoyed this post, make sure you subscribe to the RSS feed!