Home » SQL & PL/SQL » SQL & PL/SQL » sysdate
sysdate [message #272030] Wed, 03 October 2007 13:04 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hello All.

I have to insert into table dr_emp some different values based on the date.
I wrote a code like below and when I compiled it I got an error message:
ORA-01843: not a valid month

Please help. I would appreciate that.

BEGIN
    IF sysdate = '10/06/2007' THEN
        INSERT INTO dr_emp (dr_id, emp_id, dr_ckin , emp_ckin    
                           ,dr_file , emp_file  )     
        VALUES ('0000', '0000', '0000', '0000', '0', '0'  );
    ELSE
        INSERT INTO dr_emp (dr_id, emp_id, dr_ckin , emp_ckin    
                           ,dr_file , emp_file  )
        VALUES ('0000', '0000', '0000', '0000', '1', '1'  );
    END IF;
END;

Re: sysdate [message #272031 is a reply to message #272030] Wed, 03 October 2007 13:06 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You're comparing a DATE type to a STRING type. Change the date string in your IF statement into a date with the TO_DATE function.
Re: sysdate [message #272032 is a reply to message #272030] Wed, 03 October 2007 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
In Oracle characters between single quote marks is a STRING data type!
'This is a string 2007-10-03 and not a DATE!'

If/when you want a DATE data type use TO_DATE function!
Re: sysdate [message #272034 is a reply to message #272030] Wed, 03 October 2007 13:17 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you very much.
Re: sysdate [message #272094 is a reply to message #272034] Thu, 04 October 2007 00:42 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
In addition to what was said in previous posts: sysdate also contains a time-part, so sysdate is never (well, hardly ever) equal to just the date part of a date.

Do a trunc(sysdate) = to_date('01-01-2007', 'dd-mm-yyyy') to compare just the date-parts
Previous Topic: Tunned querry
Next Topic: ORA-00947: not enough values
Goto Forum:
  


Current Time: Sat Nov 09 20:02:46 CST 2024