Differencebetween 'Yyyy' and 'Rrrr' in Oracle SQL

What is the difference between 'YYYY' and 'RRRR' in Oracle SQL

YYYY gives the current year as 4 digits.

RRRR format means 2-digit years in the range 00 to 49 are assumed to be in the current century (ie have the same first two digits as the current year), and years given as 50 through 99 are assumed to be in the previous century.

Difference between RRRR and YYYY in Oracle?

  SELECT count(*) INTO P_COUNT from dual where 
       trunc( sysdate) =to_date(P_DATE,'dd-mm-yyyy')

It has to be like this.
sysdate already returns a date.

I believe ur nls_date_format is different in your sqlplus and asp connection.

So, to_date(sydate) is attempt to work on some string.. which I guess is mm-dd-yyyy or dd-mm-rr(most probably) and so there could be mismatch.

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

please run the above query in both places to catch the mismatch!

What is the difference between oracle's 'yy' and 'rr' date mask?

http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/difference-between-yyyy-and-rrrr-format-519525

YY allows you to retrieve just two digits of a year, for example, the 99 in
1999. The other digits (19) are automatically assigned to the current
century. RR converts two-digit years into four-digit years by rounding.

50-99 are stored as 1950-1999, and dates ending in 00-49 are stored as
2000-2049. RRRR accepts a four-digit input (although not required), and
converts two-digit dates as RR does. YYYY accepts 4-digit inputs butdoesn't
do any date converting

Essentially, your first example will assume that 81 is 2081 whereas the RR one assumes 1981. So the first example should not return any rows as you most likely did not hire any guys after May 1 2081 yet :-)

Oracle SQL: Wrong Year Output when using to_date

Try to insert by two types of formatting rrrr and yyyy for the year value to see the difference :

create table ticketstatus( id int, changedate date );
insert into ticketstatus values(1,to_date('30.03.19','dd.mm.yyyy'));
insert into ticketstatus values(2,to_date('31.03.19','dd.mm.rrrr'));

select changedate
  from ticketstatus;

CHANGEDATE
------------ 
30.03.0019   
31.03.2019

Indeed, you can observe this versatile situation as below :

with ticketstatus(changedate) as
(
 select '30.03.19' from dual
)
select to_date(changedate,'dd.mm.yyyy') date_with_yy,
       to_date(changedate,'dd.mm.rrrr') date_with_rr
  from ticketstatus;

DATE_WITH_YY    DATE_WITH_RR
------------    ------------
30.03.0019      30.03.2019

This is known as Year 2k problem, and the year format rrrr stems from this problem. Therefore, the date format with rrrr may be used against such issues.

Oracle - Best SELECT statement for getting the difference in minutes between two DateTime columns?

SELECT date1 - date2
  FROM some_table

returns a difference in days. Multiply by 24 to get a difference in hours and 24*60 to get minutes. So

SELECT (date1 - date2) * 24 * 60 difference_in_minutes
  FROM some_table

should be what you're looking for

Date range problems in Oracle. How do I correctly grab date ranges in this format?

You have a number of issues in your original question, I'll try to address some or all of them here:

When I try a query such as:

select DATELIST from CORE.DATE_TEST
where to_date(DATELIST) >= to_date('8/27/2015', 'mm/dd/yy') AND to_date(DATELIST, 'mm/dd/yy') <= to_date('9/27/2015', 'mm/dd/yy');

It gives me the following error: ORA-01843: not a valid month

You are not specifying a date format when you specify to_date(DATELIST) therefore it will default to the NLS setting for the default date format. You should always specify a date format whenever you use TO_DATE to prevent this from happening and ensure your date formats are explicit (and you are using yy and yet you are specifying a 4 digit year so you should use yyyy).

Try this instead (assuming DATELIST is a DATE datatype):

select TO_CHAR(DATELIST, 'DD-MON-YYYY HH24:MI:SS') -- Use whatever date format is clearest to you
  from CORE.DATE_TEST
 where DATELIST >= to_date('8/27/2015 00:00:00', 'mm/dd/yyyy hh24:mi:ss') AND DATELIST <= to_date('9/27/2015 23:59:59', 'mm/dd/yyyy hh24:mi:ss');

If DATELIST is a VARCHAR2 then you'll need to see what date format the values are stored in within the column and format the query accordingly.

The lesser-than-or-equal-to (<=) operator is only grabbing data
between 8/27/2015, and 9/26/2015. Isn't it supposed to get 8/27/2015
to 9/27/2015?

When you use TO_DATE and don't specify a time portion of the date, it defaults to 00:00:00 for the hours, minutes and seconds. If you want the full date range try to be more explicit:

SELECT to_date(DATELIST, 'dd/mm/yy hh24:mi:ss') 
    FROM CORE.DATE_TEST
WHERE DATELIST >= to_date('8/27/2015 00:00:00', 'mm/dd/yyyy hh24:mi:ss')
    AND DATELIST <= to_date('9/27/2015 23:59:59', 'mm/dd/yyyy hh24:mi:ss');

The reason the query is much faster is, I suspect, that you have an index on the DATELIST column which you are defeating when you wrap it with a TO_DATE call such as TO_DATE(DATELIST, 'mm/dd/yyyy').

If the datatype of DATELIST is DATE then leave it as such, if it's VARCHAR2 then you have some additional date formatting issues.

Hope it helps

EDIT:

I think your issue is to do with the hidden time portions of the DATE datatype DATELIST.

If you have the value:
09/27/2015 10:35:12

Even if you don't care about the 10:35:12 portion of that date, it is still there. If you then try to use the following where clause:

WHERE DATELIST >= TO_DATE('09/26/2015', 'MM/DD/RRRR')
  AND DATELIST <= TO_DATE('09/27/2015', 'MM/DD/RRRR');

Then the value won't be picked up as it is greater than:
09/27/2015 00:00:00.

You need to either up the higher bound by a day or specify the time portion e.g.:

WHERE DATELIST >= TO_DATE('09/26/2015 00:00:00', 'MM/DD/RRRR HH24:MI:SS')
  AND DATELIST <= TO_DATE('09/27/2015 23:59:59', 'MM/DD/RRRR HH24:MI:SS');

EDIT2:
Working with the OP, he has said that this has worked:

SELECT to_date(DATELIST, 'dd/mm/yy') as Date_Accessed 
  FROM CORE.DATE_TEST 
 WHERE (trunc(DATELIST) >= to_date('9/27/2015', 'mm/dd/yy')) 
   AND (trunc(DATELIST) <= to_date('9/30/2015', 'mm/dd/yy'));

Though this will defeat any index on the DATELIST column.

Inserting dates before year 1950 in Oracle

You should use:

to_char(some_date,'YYYY-MM-DD')

RRRR is for 2 digit years, assuming same century. Read here: What is the difference between 'YYYY' and 'RRRR' in Oracle SQL

---

Related Topics