Selecting last date?

Question

Hello I have a oracle db and saving date values in a column. Date format is below; --------- 30/06/2011 01/07/2011 01/07/2011 01/07/2011 02/07/2011 02/07/2011 03/07/2011 03/07/2011 03/07/2011 --------- I want to select last date's rows. In this example I wanto get 03/07/2011 's rows. But I coldnt write exactly the sql command. How can I do that? Thanks.

Zoran Horvat · Answer

Sorry the first query seems to be incorrect - it did not convert the date column at comparison but left Oracle do that on its own without proper custom format. The query should look like this: SELECT * FROM table_name WHERE TO_DATE(date_column 'DD/MM/YYYY') = (SELECT MAX(TO_DATE(date_column 'DD/MM/YYYY')) AS MaxDate FROM table_name) Zoran

yokzu · Answer

No Suthish I want to get all records of last date. Zoran's query seems ok for me:)

yokzu · Answer

Thanks a lot guys. Zoran's this sql query seems works for me. ---- SELECT * FROM table_name WHERE TO_DATE(date_column 'DD/MM/YYYY') = (SELECT MAX(TO_DATE(date_column 'DD/MM/YYYY')) AS MaxDate FROM table_name) ---- Thanks again.

Suthish Nair · Answer

Am sure i didn't get the question clear yet. Did sleepy wants only last record or last three records as shown from above sample?

Om prakash Singh · Answer

Zoran: The query works fine in sql server and In question He say last date's row so I give that solution. Thanks for telling me that top doesn't work in oracle. I was not aware of it. Thanks

Zoran Horvat · Answer

Om You can't use TOP 1 syntax with Oracle because it doesn't support it. Also your solution is based on sorting the table which is highly inefficient compared to MAX() aggregate function. Third your solution would produce only one row as the result but multiple rows might have the same maximum date value. Correct and efficient query for this problem is the one I gave above: SELECT * FROM table_name WHERE TO_DATE(date_column 'DD/MM/YYYY') = (SELECT MAX(TO_DATE(date_column 'DD/MM/YYYY')) AS MaxDate FROM table_name) It passes once to find maximum date and then passes once to extract rows with that date. If date_column has no index then this only consults the index and then extracts target rows directly from table without need for full table scan. There is no more efficient solution than that. Zoran

Om prakash Singh · Answer

You want to select last row right. use query like this select top 1 * from yourtable order by rownum desc

theLizard · Answer

Zoran: This works fine while inserting as VALUES ('2011/07/16') fails. then this is a failing with oracle I have never used varchar to store dates always datetime and have never had my statements fail ever. I can't comment too much on oracle because I do not use it and so not know the ins and out.

Zoran Horvat · Answer

Now I see that you've referred to date literals but that is completely different topic. Here we're dealing with values taken from the table and that is not a date literal... When working with date literals value must be literal and must be preceded by word DATE. For example in my SimpleTable you can write query like this: SELECT TO_CHAR(DateCol 'YYYY-MM-DD') FROM SimpleTable; But you can't write this: SELECT DATE TO_CHAR(DateCol 'YYYY-MM-DD') FROM SimpleTable; It will not convert values to DATE type although values are in format YYYY-MM-DD. You rather have to write this: SELECT TO_DATE(TO_CHAR(DateCol 'YYYY-MM-DD') 'YYYY-MM-DD') FROM SimpleTable; However you can use the literal date format in query like this: SELECT DateCol FROM SimpleTable WHERE DateCol = DATE '2011-07-16'; Thate is the main purpose of the YYYY-MM-DD format although quite limited one... Zoran

Zoran Horvat · Answer

I only need to correct myself in respect that overall default for NLS_DATE_FORMAT in Oracle is DD-MON-RR rather than DD-MON-YYYY and it formats two-digit year in range 1950-2049. When you say that Oracle stores dates in format YYYY/MM/DD that is simply not true because internally date is stored in binary format. Here is the relevant link with details: http://www.dbasupport.com/oracle/faq/Detailed/117.shtml Now I have installed fresh Oracle 10g Express database and with no DBA modifications this query: SELECT value FROM v$nls_parameters WHERE parameter IN ('NLS_DATE_FORMAT' 'NLS_TERRITORY' ); Returns values: AMERICA DD-MON-RR These are overall defaults for Oracle and that is what you get when you install it. Off course you can change parameters after installation but then it would not work as a default but rather a custom which is equal to using TO_DATE in every query only doing it at administration level. But to be honest I would never rely my logic on NSL_DATE_FORMAT setting simply because I mostly often do not have control over database settings. Now about inserting date. I have created a table like this (all on the freshly installed Oracle XE): CREATE TABLE SimpleTable ( DateCol DATE ); And then inserted the value like this: INSERT INTO SimpleTable(DateCol) VALUES ('16-JUL-11'); This works fine while inserting as VALUES ('2011/07/16') fails. Anyway it is a bad idea to store dates as varchars in any kind of database. As I already stated before dates should be stored as strong type and then converted to strings at presentation stage where it is simple and effective and can be done in any date format desired at the moment. Zoran

theLizard · Answer

Zoran oracle will store date time as yyyy/mm/dd this is a universal method that eliminates the need to do most conversions. Sending a query like INSERT INTO mytable (mydate) VALUES( '2001/03/07') will work without having to do something like INSERT INTO table_dt VALUES(4 TO_DATE('01-JAN-2003' 'DD-MON-YYYY')); the TO_DATE is obviously an oracle db function to return a date literal and insert it into the table in the format specified in 'DD-MON-YYYY' AND SELECT * FROM mytable WHERE mydate = '2011/07/03' WILL return the date wanted without having to do things like SELECT * FROM table_name WHERE TO_DATE(date_column 'DD/MM/YYYY') = (SELECT MAX(TO_DATE(date_column 'DD/MM/YYYY')) AS MaxDate FROM table_name) The DATE data type stores date and time information. Although date and time information can be represented in both character and number data types the DATE data type has special associated properties. For each DATE value Oracle Database stores the following information: century year month date hour minute and second. You can specify a date value by: Specifying the date value as a literal Converting a character or numeric value to a date value with the TO_DATE function A date can be specified as an ANSI date literal or as an Oracle Database date value. An ANSI date literal contains no time portion and must be specified in exactly the following format: DATE 'YYYY-MM-DD' The following is an example of an ANSI date literal: DATE '1998-12-25'

Zoran Horvat · Answer

Storing dates as YYYY/MM/DD has nothing to do with this conversion simply because Oracle's default date format is DD-MON-YY (e.g. 15-JUL-11). With that format it is always clear what is day and what is month because month is encoded using its abbreviated name. Formats YYYY/MM/DD and YY/MM/DD are useful in a totally different aspect. With such formats chronological and lexicographical orders are the same so you can sort varchars in the same way as you would sort dates. Using any format other than DD-MON-YY in Oracle means that you have to convert varchar into date manually using TO_DATE function. Anyway I always prefer storing dates as dates rather than varchars. It is better (and offers wider formatting options) to format string representation of the date on output rather than to keep formatted date strings in the database. Zoran

theLizard · Answer

you should be storing dates in this format yyyy/mm/dd otherwise you get all sorts of problems because of locale settings on computers You then wont get the types of errors you are.

yokzu · Answer

Its getting no error this time but getting no rows too :(

Zoran Horvat · Answer

This error indicates that you have rows with invalid date format which is probably a reversed order of day and month. Try this query: SELECT * FROM table_name WHERE TO_NUMBER(SUBSTR(date_column INSTR(date_column '/' 1 1) + 1 INSTR(date_column '/' 1 2) - INSTR(date_column '/' 1 1) - 1)) > 12 It will extract the second number from the date which should be the month and then convert it to number in order to test whether it's larger than twelve or not. Any row having month number greater than 12 will be selected and those are the invalid dates. Zoran

yokzu · Answer

Thanks for your reply zoran. My sql query is below but I am getting ORA-01843: not a valid month error. SELECT * FROM table_name WHERE date_column = (SELECT MAX(TO_DATE(date_column 'DD/MM/YYYY')) AS MaxDate FROM table_name)

Zoran Horvat · Answer

Try something like this: SELECT * FROM MyTable WHERE DateColumn = (SELECT MAX(TO_DATE(DateColumn 'DD/MM/YYYY')) AS MaxDate FROM MyTable) Zoran