LPS wrote:What utter nonsense.
AND TO_DATE(pac.segment7,'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE)
Firrst check the format of SYSDATE. Here you misplaced year in the first position instead of last position. First check the format before comparing by converting
LPS wrote:Again you're talking nonsense.
Check your date format before comparing with sysdate.
SQL > SELECT SYSDATE FROM DUAL;
And then use the format specifiers according to, in the comparison for segment7 to with sysdate.
Make sure all the data in segment7 are in the same format.
See... there was no need to specify the date format in the to_date function to match the format that sysdate was displayed in.
SQL> select sysdate from dual; SYSDATE ------------------- 04/12/2012 10:53:04 SQL> SQL> select 1 from dual where to_date('2012/12/04','YYYY/MM/DD') = trunc(sysdate); 1 ---------- 1
SQL> ed Wrote file afiedt.buf 1* select 1 from dual where to_date('0000/12/04','YYYY/MM/DD') = trunc(sysdate) SQL> / select 1 from dual where to_date('0000/12/04','YYYY/MM/DD') = trunc(sysdate) * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Kumar Kasinathan wrote:Well, if that really is the data for that record then it should work ok (without error)...
The "segment7" column is varchar2 type only and it holds the value as "2013/06/14 00:00:00" .
The employee_number is also varchar2 type.
Are there any other characters in that varchar2 data?
SQL> ed Wrote file afiedt.buf 1* select 1 from dual where to_date('2013/06/14 00:00:00','YYYY/MM/DD HH24:MI:SS') = trunc(sysdate) SQL> / no rows selected
Kumar Kasinathan wrote:But what about the data?
Thanks again BluSahdow,
The dump function returs same lengt i.e "Typ=1 Len=19:" for all records.