Re: Comparing 2 dates

In article <38916ce1_at_pull.gecm.com>, Nicky Taylor <nicky.taylor_at_gecm.com> writes
>I have a 8 character string field which represents a date in the format
>'YYYYMMDD' and I have a date field. I am trying to subtract one from the
>other by doing a TO_DATE on both fields with the format 'YYYYMMDD'. i.e.
>
>TO_DATE(date1,'YYYYMMDD') - TO_DATE(date2,'YYYYMMDD'))
>
>I keep getting ORA errors complaining about the formatting.
>
>Any help appreciated.
>
>Nicky.
>
>

Nicky,

select to_date('20000101', 'YYYYMMDD') - to_date('19991212','YYYYMMDD') from dual

You say that one is already a date field? In that case you only need:

select my_date_field - to_date('19991212','YYYYMMDD') from dual

If you try passing a date into the to_date function, it will first try to convert it to a string in nls_date_format. It then tries to do a to_date on that string using the format string that you've given it - which probably does not match the nls_date_format. I'd guess that this is where your problem lies.

Andy
--
Andy Hardy. PGP key available on request