1

I am writing a Oracle SQL Statement for a report. User is able to put in FROM / TO Dates and I compare it to the issued rows with (simplyfied):

… where changedate < to_date(user_input, 'dd.MM.YYYY')…

Field "changedate" is Datatype DATE in this Table.

Code:

select to_date(changedate, 'dd.MM.YYYY') from TICKETSTATUS;

The field usually contains 

30.03.2019 21:50:48

but my Output with converting it with the Code is:

30.03.0019

Why does it do this? I want '2019' as the year but it seems to erase the first 2 numbers? Can somebody explain and help me fix that issue? Thanks!

Improve this question
3
  • Have a look at this answer and try out options - stackoverflow.com/questions/6812916/… Commented Jun 24, 2019 at 6:31
  • 1
    Why are you storing DATE values in a VARCHAR column? That is a really bad idea to begin with. Commented Jun 24, 2019 at 6:49
  • 2
    The problem here is that you are calling to_date on a column which is already a date. Commented Jun 24, 2019 at 8:14

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

The actual issue is the to_date on something that is already in date format. Don't do that, ever. Using rrrr in this case just masks the issue. Remove the to_date instead - or, if you're wanting to remove the time part, use trunc() on the date.
You should NEVER use TO_DATE on a DATE column as this will involve an implicit conversion which is effectively TO_DATE( TO_CHAR( changedate, (SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' ) ), 'dd.mm.rrrr' ) and if the NLS_DATE_FORMAT is mm.dd.yyyy (month and day swapped in US format) then unexpected things can occur.
yes thank you @Boneist. Now, I inserted the by applying to_date for string values such as '30.03.19' and '31.03.19'.
The data you are using is wrong as the OP stated that changedate has the DATE data type and you have changed it to a string data type. If you change the input to a date literal DATE '2019-03-30' and use ALTER SESSION SET NLS_DATE_FORMAT = 'mm.dd.rrrr'; then you will see one of the issues we are talking about with this answer and why it is wrong to use TO_DATE on a date. db<>fiddle
@MT0 as clearly you should see I'm converting string values to date values during the insertion such as to_date('31.03.19','dd.mm.rrrr').
|
2

[TL;DR]

  1. Do not use TO_DATE on a DATE data type.
  2. Your problem may be on data entry rather than on output as you are probably getting 2-digit year inputs when a 4-digit year is expected.

You do not need to convert change_date to a DATE using TO_DATE as it is already a DATE data type. Just use:

select changedate
from   TICKETSTATUS;

If you want your date values to have a specific format then use TO_CHAR (not TO_DATE):

SELECT TO_CHAR( changedate, 'dd.mm.yyyy' )
FROM   TICKETSTATUS;

but my Output [] is:

30.03.0019

That may be because you have an error elsewhere in your code that is probably inserting dates from a string with a 2-digit year where a 4-digit year is expected:

CREATE TABLE TicketStatus ( changedate DATE );

INSERT INTO TicketStatus ( changedate ) VALUES ( TO_DATE( '30.03.19', 'dd.mm.yyyy' ) );

Then the year will be 0019.

However, if you use the RR format model for years (or, depending on appropriateness, the YY format model):

INSERT INTO TicketStatus ( changedate ) VALUES ( TO_DATE( '30.03.19', 'dd.mm.rr' ) );

Then the year will be 2019.

SELECT TO_CHAR( changedate, 'dd.mm.yyyy' )
FROM   TicketStatus

Outputs:

| TO_CHAR(CHANGEDATE,'DD.MM.YYYY') |
| :------------------------------- |
| 30.03.0019                       |
| 30.03.2019                       |

db<>fiddle here

It could also be because the NLS_DATE_SETTING in your session is set to dd.mm.yy and the implicit conversion made using TO_DATE on a date column is removing the century but then ALL your dates would have the century 00 (rather than just some of the dates).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.