Oracle 11g Date Issue – Oracle Date Not Returning Correct Data

oracleoracle-11g

I don't know whats going on here, but here is my table (budget) with select * from budget :

|budget_id|as_of_date|quarter_1|quarter_2|quarter_3|quarter_4|
|1        |15-MAY-16 |400      |100      |100      |100

So say if I wanted to calculate sum for all quarters on the given date(15th May of 2016) or before I would use this query :

select as_of_dt from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

This query returns nothing:

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

However if I add one day to this :

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-16', 'yyyy-MM-dd');

I get the right result 700. I want to get all budget records before or equal to a given date string, why is the equal not working?

This might be a hint but I don't get it :

select to_date(as_of_dt, 'yyyy-MM-dd') from budget;
=> 16-MAY-15

select as_of_dt from budget;
=> 15-MAY-16

Somehow the year gets flipped or whatever is happening here, what do I do to get the right output with both equal and less than a given date ?

Best Answer

The to_date function creates a date with a time. If I remember well this is 12:00. If the date in your table has a different time then this is also taken into account. When you want to test dates then you better put the trunc() function around your date like:

select as_of_dt
from   budget
where  trunc(as_of_dt) <= to_date('2016-05-15', 'yyyy-MM-dd');

Now the time part is the same in both dates.

Related Solutions

Decode query truncates a date

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

Oracle TRANSLATE Not Returning Expected Result

Phil is right, "No, there isn't a limit", "it /should/ just work", and " this will be difficult to answer without copies of the data or another way to reproduce". Here is a demonstration:

/*
drop table table_a;
drop table temp_table;
create table table_a as 
    (select level id, to_char(level) column_to_update from dual connect by level <=200);
create table temp_table as (select level id from dual connect by level <= 200);
update table_a set column_to_update='2ñ' where column_to_update='2';
*/

SELECT * FROM table_a;

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') 
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

SELECT * FROM table_a;

You could try adding a WHERE clause to verify that TRANSLATE is actually changing rows.

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') 
WHERE ID IN (SELECT ID FROM TEMP_TABLE)
AND column_to_update <> TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') ;

If this returns 200 rows then you might not be looking at the correct rows. If it returns zero rows then the TRANSLATE function is not changing the data.

Best Answer

Related Solutions

Decode query truncates a date

Oracle TRANSLATE Not Returning Expected Result

Related Question