Selecting last date?

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

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.

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

You want to select last row right.

use query like this,

select top 1 * from yourtable order by rownum desc

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.

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

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

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'

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

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.

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

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

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)

Try something like this:

SELECT *
FROM MyTable
WHERE DateColumn = (SELECT MAX(TO_DATE(DateColumn, 'DD/MM/YYYY')) AS MaxDate FROM MyTable)

Zoran