Date Help 1

[dbomrrsm]

I have a table (tableA) that holds a date field and I want everything from tableA where the date field is greater that the maximum date selected from a different table tableB.

The difficulty I have is that tableA's date field is a date data type and the field from tableB is a varchar2 (23).

Example data from tableA

select max(date_field) from tableA - 13/12/2006 14:37:15

select date_field from tableB - 2006-12-11 16:55:51.000

How do I write the below query:

select *
from tableA
where date_field > 
(select date_field from tableB)

BTW tableB only has one row so the select only returns one value.

I have TO_CHAR'ed and TO_DATE'd both fields to death and cant get it to work.

TIA

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Dagon]

select *
from tableA
where date_field >
(select to_date(date_field, 'YYYY-MM-DD HH24:MI:SS') from tableB)

 

[dbomrrsm]

Dagon

Thanks but your code produces error:

ORA-01830: date format picture ends before converting entire input string


[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

Dagon

Thanks - got it by doing:

select *
from tableA
where date_field > 
(select to_date(substr(date_field,1,19), 'YYYY-MM-DD HH24:MI:SS') from tableB)

Cutting off the milliseconds in the varchar2 field.



[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Beilstwh]

Unless there is only one row in tableb, you are not using the max date. Use

select *
from tableA
where date_field >
(select max(to_date(substr(date_field,1,19), 'YYYY-MM-DD HH24:MI:SS')) from tableB);

Bill
Oracle DBA/Developer
New York State, USA