Issue with To_Date Function with Sysdate

to_date function with sysdate

use this:

 select TO_CHAR(sysdate, 'DAY') FROM DUAL;

you are using this :

 to_date(sysdate, 'DD-MON-YYYY') 

which is giving you date=1/1/0013 which is sunday

issue with to_date function with sysdate

I want to explain why you get different results.

See this sqlfiddle

As it is already said, sysdate is seen as DATE type and you are doing an implicit conversion when

select to_date(sysdate, format) from dual;

because first parameter of to_date should be varchar type the system does:

select to_date(to_char(sysdate), format) from dual;

because your implicit date format is 'DD-MON-YY', your query goes into:

SELECT TO_CHAR(to_date('01-JAN-13', 'DD-MON-yy'), 'DAY'),
  TO_CHAR(to_date('01-JAN-13', 'DD-MON-yyyy'), 'DAY'),
  TO_CHAR(to_date('01-JAN-13', 'DD-MON-rr'), 'DAY'),
  TO_CHAR(to_date('01-JAN-13', 'DD-MON-rrrr'), 'DAY')
FROM dual;

the second to_date, because yyyy is a full thousands year format, goes to '01-JAN-0013' which is 13AD and probably is SUNDAY :)

To_CHAR(SYSDATE, ...) ---- TO_DATE

I can't see a problem with your code, assuming quand is of type date, but why not just do this?

create or replace TRIGGER tr_after_logon
AFTER LOGON ON SCHEMA

BEGIN

  INSERT INTO activity_log (numero, idsession, action, detail, objet, quand) 

 VALUES (seq_activity.nextval, USERENV('SESSIONID'), 'CONNEXION', ORA_DATABASE_NAME,
  ORA_CLIENT_IP_ADDRESS, SYSDATE);

END;

How can I convert a complex sysdate + something else with a to_date function?

the output you see is converted to a char format. this is dictated by the NLS_DATE_FORMAT setting. internally, the date will still have the time format associated. so do this to see it:

SQL> select sysdate from dual;

SYSDATE
---------
08-NOV-12

SQL> alter session set nls_date_format='dd/MON/yy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
------------------
08/NOV/12 11:41:46

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 to_date() return incorrect date with +2 days

The problem is you are not matching the format you are providing.

Year is provided as 18 and TO_DATE expects 2018.

Below should work fine:

select to_date('11-MAY-18', 'DD-MON-YY') from dual;

OR

select to_date('11-MAY-2018', 'DD-MON-YYYY') from dual;

JSP Page with Oracle Query with to_date function error

You can convert your query to the style below against date formatting problems :

SELECT * 
  FROM message_detail_tmp 
 WHERE updated_date >= date'2019-02-27'
   and updated_date <  date'2019-03-01'

If you need hours, minutes and seconds you can try the following cases :

SELECT * 
  FROM message_detail_tmp 
 WHERE updated_date >= timestamp'2019-02-27 15:00:00'
   and updated_date <  timestamp'2019-02-28 16:00:00'

or

SELECT * 
  FROM message_detail_tmp 
 WHERE updated_date between timestamp'2019-02-27 15:00:00'
                        and timestamp'2019-02-28 16:00:00'

Oracle date comparison using to_date not working

From your question and comments, it appears that this is the sequence of events which happened.

You did the following INSERT into your table:

INSERT INTO test1.tasty1 VALUES ('sat', SYSDATE)

Keep in mind that dates in Oracle have both a date and a time component. So even though you did insert the date '2016-12-08' you also inserted a time component. As a result, the following query is not returning any records:

SELECT * FROM test1.tasty1 WHERE DOJ = '2016-08-12'

This is probably because you never specified the time component, and therefore the record you inserted earlier is not matching. If you want to compare only the date portion, you can use TRUNC as follows:

SELECT * FROM test1.tasty1 WHERE TRUNC(DOJ) = '2016-08-12'

The solution to your problem moving forward would be to wrap SYSDATE with TRUNC during the insert, if you really only want to deal with the date components.

By the way, the format '08-DEC-16' used as a literal will not be recognized by Oracle as a valid date. Instead, use '2016-12-08'.

---

Related Topics