Decode query truncates a date

date formatoraclequery

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT 
    DECODE(
        TO_CHAR(MIN(DATE), 'YYYYMMDD')
        , ''
        , NULL
        , TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD')
    ) 
FROM TABLE 
WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD') 
FROM TABLE 
WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified. Can someone shed some light on this? This is a problem since the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: The two conversions (from date to char and then again to date) here is due to the fact that the query as I put it here is really two different queries: the first one inserts a DATE in an auxiliary table's column which has a CHAR format; then another query takes this data to the final table, where it's stored as a DATE, and hence the second conversion. I simplified it here to make it more understandable. The DECODE is necessary as some of the rows don't have data on the date column.

Anyway, I think the best solution is to do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD' as suggested by @Phil and @LeighRiffel, and then both queries (with and without DECODE) return the same date. Another possible workaround would be to leave the final data as CHAR instead of converting it to DATE, but this would have a large impact on the application I'm working on, so I'm sticking to the ALTER SESSION ... way.

Best Answer

As a_horse indicated, select min(date)... is sufficient. The DECODE and multiple conversions just slow things down without accomplishing anything. Even as parts of queries, you should eliminate this needless processing.

As Phil indicated, the displaying of dates is controlled by the NLS_DATE_FORMAT parameter and can be altered to display dates as you wish. This does change how the queries run (unless there are missing format specifications or SQL injection is being done). Change the display format like this:

 ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';

When doing a to_date on a two digit year, use YY when you want the first two digits of the year to match the current year, but use RR when you want the first two digits to be 20 for years ending in 0-49 and 19 for years ending in 50-99. See the SQL Language Reference for more information:

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

That is:

If the specified two-digit year is 00 to 49, then

If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.

If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

If the specified two-digit year is 50 to 99, then

If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

The following examples demonstrate the behavior of the RR datetime format element.
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 
Year
---- 
1998 

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 
Year
----
2017

Related Solutions

Convert Log table days in a state

select item, 
       sum(duration),
       moved_to
from (
  select item, 
         lead(move_date)  over (partition by item order by move_date) - move_date as duration,
         moved_to
  from movement         
) t
group by item, moved_to
order by item, moved_to;

SQLFiddle example: http://www.sqlfiddle.com/#!4/dff2c/1

Struggling with complicated date query

I'll do it in stages to make it easier to understand.

I put your data into a table called EX_DATA - added a primary key as I assume you'll have one and it makes things a little easier:

create table ex_data
(
  contract_id number(18),
  expire_dt date,
  term_notice number(18),
  notice_freq varchar(20),
  notice_months number
);


insert into ex_data values(1,TO_DATE('01-MAR-2005','DD-MON-YYYY'), 60, 'YEARLY', 12);

insert into ex_data values(2,TO_DATE('01-DEC-2007','DD-MON-YYYY'), 30, 'MONTHLY', 1);

insert into ex_data values(3,TO_DATE('28-FEB-2008','DD-MON-YYYY') ,30, 'YEARLY', 12);

The next expiry date from the first given date will be:

select add_months(expire_dt, notice_months) as next_expiry
from ex_data;

Then, it follows that the notice period is a bit of easy date arithmetic too:

select add_months(expire_dt, notice_months) as next_expiry, 
       add_months(expire_dt, notice_months) - term_notice as notice_date
from ex_data;

Using DUAL as a source, generate expiry dates for lots of years:

select expire_dt as first_expiry_date,
       l as expiry_num,
       add_months(expire_dt,l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
order by l;

The next expiration date, and the date they should have been contacted for renegotiation:

select first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as renegotiation_date
from
(
select expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates >sysdate
and all_expiry_dates - term_notice < sysdate 
;

But, that's not what I think you're asking. You want the next renewal date where the contact period hasn't yet been hit, which can be done as follows:

select * from (
select contract_id, first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as notification_date, ROW_NUMBER( ) OVER (PARTITION BY
contract_id ORDER BY all_expiry_dates) r
from
(
select contract_id, expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates, notice_months
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates - term_notice > sysdate 
)
where r=1;

Can probably be done with some simple SYSDATE maths along with the source data, but my brain is a little slow this evening ;)

Best Answer

Related Solutions

Convert Log table days in a state

Struggling with complicated date query

Related Question