Link to home
Start Free TrialLog in
Avatar of newtoperlpgm
newtoperlpgmFlag for United States of America

asked on

select max date from varchar field

I have a table with a varchar(2) column that stores date information as a varchar.  I need to select the records with the max date, ie. the most current record, how can I do that with a varchar field?  The reason it is a varchar(2) field is because data is loaded into the table from flat files and then manipulated.
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

use to_date(..) inside the max function and the format mask accordingly :

select max(to_date(your_varchar_field,'ddmmyyyy')) from your-table;

so if your varchar data is in the 20130201 data format, then try the above sql query to see if it works.
What version Oracle, and how granular are the date values?
it would be easier to answer the question the directly if you can provide sample data to understand how your varchar2 data looks. This would help one to give the query with the required format in the to_date(..) conversion.
a typo there in my first post... Corrected one is :

use to_date(..) inside the max function and the format mask accordingly :

select max(to_date(your_varchar_field,'ddmmyyyy')) from your-table;

so if your varchar data is in the 01022013 data format, then try the above sql query to see if it works.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>The reason it is a varchar(2) field is because data is loaded into the table from flat files and then manipulated.

Personally, I would rethink this.  You can load from a flat file into a date column.  Real easy if you are using sql loader.  Dates are likely easier to 'manipulate' than a varchar2.
in SQL:


SELECT MAX(CONVERT(smalldatetime, columna)) AS [RESULT]


Oracle: (can't verify myself)

SELECT MAX(To_Date(columna, 'MMDDYY')
Only date varchar(2)? What about Month and Year?

assuming you have different columns for month varchar(2) and year varchar(4) you may try
select MAX(TO_DATE( <date_col>||<month_col>||<year_col>   , 'DDMMYYYY') from DUAL

Open in new window

It would certainly be better in you could upload the data into a date field, but to_date() can be used to convert strings to dates (plenty examples above)

Like ajexpert however I'm confused: "a varchar(2)" = one column only; but it carries Year + Month + Day? Could you post a few samples of this field? (or fields?)
I would think that you are using a VARCHAR2 field because of the possibility of invalid dates in the column.  If any invalid data exists in the column, using TO_DATE is just going to cause an error.

Depending on the format of the date in the field, it may be possible to simply use a MAX function, but that again depends on the format.

As suggested, I would rethink your design.  The flat file would be loaded into this table and that table should be treated as a staging table.  The data should be validated in this staging table and then moved to the "real" table with all the correct data types.  Anything that fails validations will be left behind and have to be corrected before moving.
Avatar of newtoperlpgm

ASKER

Here is an example of my data in my date field that is of varchar(2) type

08/28/1012 13:00
Varchar2 cannot hold "08/28/1012 13:00"  i.e 16 byte data

Are you sure 08/28/1012 13:00 is in table column having data type as VARCHAR2?
My Oracle table column is of data type varchar2 20 byte, and the data is as mentioned above,

08/28/1012 13:00
can you try this?

select MAX(TO_DATE( <datecol>   , 'MM/DD/YYYY', 'HH24:SS') from <your table>

Open in new window

It doesn't work, I get the following error:
Execution (6: 49): ORA-12702: invalid NLS parameter string used in SQL function
Here is corrected sql:

select MAX(TO_DATE( <datecol>   , 'MM/DD/YYYY HH24:MI')) from <your table>

Open in new window

That worked for selecting the max date from my table with the varchar(2) date column.  So, if I wanted to select the record(row) with the most current or max date from an oracle table in my where clause, would that work?
Thank you.
It should. Post the query if u are not getting desired results. Will modify the query if needed

HTH
select  fielda, fieldb, fieldc  from tablea tableb
where tablea.id = tableb.id
and MAX(TO_DATE( LOAD_DATE, 'MM/DD/YYYY HH24:MI'))  (I want to select the row with the most current date)

I get the following error:

ORA-00934: group function is not allowed here

Thanks.
On phone so please excuse for typos

Can u tell me which table ( a or b )has date that u want to compare with max load date?
select  fielda, fieldb, fieldc, MAX(TO_DATE( LOAD_DATE, 'MM/DD/YYYY HH24:MI'))
from tablea tableb
where tablea.id = tableb.id
GROUP BY fielda, fieldb, fieldc  

not sure on grouping order, you will have to figure out what works right.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
***ajexpert

On phone so please excuse for typos

Can u tell me which table ( a or b )has date that u want to compare with max load date?

table a
a.load_date
Just to clarify what I want to do, I want to select the record with the most current date, but I do not want to select the date, for example...

Select fielda, fieldb, field c from tablea, tableb
where tablea.fielda = tableb.fielda
and tablea.load_date is the max date (most current date)

In other words, I need my max (to_date) in my where clause, I am not selecting it.

Make sense?
Thank you.
SELECT fielda, fieldb, fieldc
FROM
(select  fielda, fieldb, fieldc, MAX(TO_DATE( LOAD_DATE, 'MM/DD/YYYY HH24:MI'))
from tablea tableb
where tablea.id = tableb.id
GROUP BY fielda, fieldb, fieldc)
>>I want to select the record with the most current date, but I do not want to select the date, for example...

Isn't that what I did in my test in http:#a38882738?  Just remove the fields from the select that you don't want.
I believe this is close to your answer
select
   tablea.fielda  -- nb uncertain which tables the fields come from
 , tablea.fieldb  -- please correct the table.field references as needed
 , tablea.fieldc 
 , tableb.*
from tablea
inner join tableb ON tablea.fielda = tableb.fielda
    /* assumes the following format in tablea.load_date
    08/28/1012 13:00
     */
where tablea.load_date = (select MAX(TO_DATE(tablea.load_date, 'MM/DD/YYYY HH24:MI')) from tablea)

Open in new window

Because I was unable to yield the results I wanted, I was convinced by this post to insert the data from my staging table into a table with date columns into date data type columns. This will make it possible to select the row with the  max(datefield).  Thank you.
I will give that a try PorletPaul....and post the results.
Thanks.
and you might distribute points to those contributing to that decision/result?
>>This will make it possible to select the row with the  max(datefield).

It's possible without this but as I posted in http:#a38866383 it is probably best.

Even with a date field, you will likely still have the same issues.  Converting from a string to a date was the easy part.

If you cannot do it with a varchar2 as I have demonstrated, please post some actual data and what you are trying.

Add to the test case I posted in http:#a38882738

It uses a varchar2 and has two rows that tie for max date.

It returns those two rows.  It also only hits the table once.


Sorry RustyZ32 but your solution provides the max grouped.  Not the rows that equal the max date.    As I understand the requirements and using my test case above they want the two rows with 'c' and 'd'.
correction need to_date conversion on both sides of the where clause
with tablea as (
                select 'a' as fielda, '08/28/1012 13:00' as load_date from dual
                union all
                select 'b' as fielda, '08/28/1012 13:01' as load_date from dual
                union all
                select 'c' as fielda, '08/28/1012 13:02' as load_date from dual
                union all
                select 'd' as fielda, '08/28/1012 13:02' as load_date from dual
                )
select
   tablea.fielda  -- nb uncertain which tables the fields come from
from tablea
--inner join tableb ON tablea.fielda = tableb.fielda
    /* assumes the following format in tablea.load_date
    08/28/1012 13:00
     */
where TO_DATE(tablea.load_date, 'MM/DD/YYYY HH24:MI') = (select MAX(TO_DATE(tablea.load_date, 'MM/DD/YYYY HH24:MI')) from tablea)

Open in new window

in this example - ignoring tableb, results:
 FIELDA    
 --------- 
 c         
 d         

Open in new window

although we might be close on the priginal question, you may still be advised to alter your data load approach by staging then reaching tables with true datetime information
This query yields the most current load_date, period, not the most current load_date for the actual codeno, which is what I really want.

select a.codeno, b.sample_no, a.load_date
from tbl_header a, tbl_sample b
where a.codeno = b.codeno
and TO_DATE(a.load_date, 'MM/DD/YYYY HH24:MI') = (select MAX(TO_DATE(tbl_header.load_date, 'MM/DD/YYYY HH24:MI')) from tbl_header)
In the future it would help a LOT if you provided sample data and expected results.

Then we can get a visual idea of what you want and set up out own test cases.

Guessing about what you want, here is an updated test.

If it is not correct, please add to it with whatever you need to then post your expected results.

drop table tbl_header purge;
drop table tbl_sample purge;

create table tbl_header(codeno char(1), load_date varchar2(17));
create table tbl_sample(codeno char(1), sample_no number);

insert into tbl_header values('a','08/28/1012 13:00');
insert into tbl_header values('a','08/28/1012 13:01');
insert into tbl_header values('a','08/28/1012 13:02');
insert into tbl_header values('b','08/28/1012 13:03');
insert into tbl_header values('b','08/28/1012 13:04');
insert into tbl_header values('b','08/28/1012 13:05');
insert into tbl_header values('c','08/28/1012 13:06');
insert into tbl_header values('d','08/28/1012 13:07');

insert into tbl_sample values('a',1);
insert into tbl_sample values('b',1);
insert into tbl_sample values('c',1);
insert into tbl_sample values('d',1);
commit;

select codeno, sample_no, load_date from (
select a.codeno, b.sample_no, a.load_date,
	rank() over(partition by a.codeno order  by to_date(load_date,'MM/DD/YYYY HH24:MI') desc) rank
from tbl_header a, tbl_sample b
where a.codeno = b.codeno
)
where rank=1
/
                                            

Open in new window

I apologize for not posting desired results, I really thought it would be simple and that I just didn't know how.
Below is the data of rows for which I only want my query to yield the 2nd row of data with the most current date.  


12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 08/28/2012 13:00    
12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 02/12/2013 14:03
12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 09/04/2012 17:00
The expected results are a good start but without the raw data, it is still hard to get you where you need.

I believe my last post will work or you but without the raw data, I'm still guessing.

If my last post doesn't work for you, can you add to it?

Feel free to change the column names, data values, add columns to the tables, etc...

Then we can post 100% working models based on your data and expected results.  No more guessing.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>/* using row_number()

The potential problem with row_number is if two dates for a codeno are the same.  It will return one where rank will return both.

Guess it's up to the asker which approach to take.
To clarify the following 3 rows exist (raw data) and I want to yield the 2nd row as my query result

Row 1  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 08/28/2012 13:00    
Row 2  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 02/12/2013 14:03
Row 3  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 09/04/2012 17:00

I am trying the two solutions by slightvw and PortletPaul
>>following 3 rows exist (raw data)

Based on the last query you posted, you have data in two tables.  You are also selecting 3 columns: select a.codeno, b.sample_no, a.load_date

I mean I can create dummy tables that have made-up values that look like:
12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 08/28/2012 13:00

And give you the second row, but will that be good enough?  I mean I created a single table and provided the rows with the max date way back up in http:#a38882738

My new test based on your raw data would look exactly the same.

>>I am trying the two solutions by slightvw and PortletPaul

They are basically the same.  The only difference is what do you want if you have data that looks like (notice the time on row2 and row3):
Row 1  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 08/28/2012 13:00    
Row 2  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 02/12/2013 17:00
Row 3  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 09/04/2012 17:00
The potential problem with row_number is if two dates for a codeno are the same.  It will return one where rank will return both.
Seemed I was working on my revision slightly behind yours :) and not based on any data (just grey matter) - but returning only one record in a tie is what I think is being asked for - I stress "think".
>>returning only one record in a tie is what I think is being asked for

Coin flip.  ;)
Thank you for all the information, it was all very helpful.
newtoperlpgm,

Just out of curiosity, what solution did you want?

Using your data that I modified:
Row 1  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 08/28/2012 13:00    
Row 2  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 02/12/2013 17:00
Row 3  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 09/04/2012 17:00

Do you want row2 or row 2 and 3 since the times match?
The dates don't match, row2 has a date of 2/12/13, the most current date.
Thanks.
>>The dates don't match

hehe... oops.  Typoed when I copied your example.

So if you had two dates that matched exactly, do you want one row or two (now they match)?

Row 1  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 08/28/2012 13:00    
Row 2  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 02/12/2013 17:00
Row 3  12082705 0 NCo 0 off site Bep1 GroAB 092469 RAP 02/12/2013 17:00
I only want one row, the one with the most current date/timestamp.
Thanks.
Oh, by the way, in my data there will never be rows with exact date/timestamps, that is why I am trying to get the most current one.
>>will never be rows with exact date/timestamps

Never say never...  as soon as you do, you will find the exception.  Assume there might be someday and plan for it now.

>>the one with the most current date/timestamp.

What if there is a tie someday?

Do you want one row or two if you do have the exact same time in two rows?
If there is a tie, and there probably will be someday, I would want only one.  Thank you.
>>If there is a tie, and there probably will be someday, I would want only one.

Then use the row_number version provided by PortletPaul.  My rank version will return two.