Problem with CURRENT_DATE

what is the data type of the column?
what is the UPDATE statment, please?

as a guess, I assume your column is VARCHAR2 and not datetime, hence the "cut" possible.
in which case, you either should change the column to date data type,
or use the TO_CHAR() function:

UPDATE yourtable
  SET yourfield = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'))
WHERE ...

Select allOpen in new window

swami_varahagir,
    The way you "moved it to a stored procedure" or the way you use it in the update could cause the problem.  If Oracle does an implicit data conversion (and the default date format isn't set as you believe it to be), then it strips the time portion, returning (usually) DD-MON-YYYY.  the other possiblity is an accidental TRUNC function being used.  If you could show us the update and the stored proc, we might be able to help.

Good luck!

The column is date data type. Here is the code snippet.

CREATE OR REPLACE PROCEDURE STN01.Upt_Sesn (SesnGuid IN VARCHAR2, Mins IN INT)

AS
  SesEnd DATE;
BEGIN

  SesEnd := CURRENT_DATE;
  IF (Mins > 0) THEN
       SesEnd := dateAdd('MI', CURRENT_DATE, Mins);
  END IF;
  UPDATE TAB1
     SET TAB1_END_DTE = SesEnd
   WHERE GUID_ID = SesnGuid;
END;
/
exec STN01.Upt_Sesn ('GUID',0);
drops the time part from the column.
It works fine if I issue a update statement only as below
UPDATE TAB1
     SET TAB1_END_DTE = CURRENT_DATE
   WHERE GUID_ID = 'GUID';
COMMIT;
It stores the time part as well.

DROPS in ther sense. It resets it to Midnight say if I update now the date should be 17-JULY-2008 14:39:44 but it stores as 17-JULY-2008 00:00:00.

Thanks,
Swm...

it's your dateadd function.

It must be stripping your time off

instead of calling that function

just do the math yourself

UPDATE TAB1
     SET TAB1_END_DTE = currrent_date + greatest(0,mins)/1440
   WHERE GUID_ID = SesnGuid;

That's MySQL Syntax, not Oracle.  CURRENT_DATE only has the date in pl/sql, not the time in it.  SQL is different.

Try replacing the if and get rid of sesend:
  IF (Mins > 0) THEN
       UPDATE TAB1
     SET TAB1_END_DTE = dateAdd('MI', SYSDATE, Mins);
   WHERE GUID_ID = SesnGuid
 else
  UPDATE TAB1
     SET TAB1_END_DTE = SesEnd
   WHERE GUID_ID = SYSDATE;
end if;

Good luck!

DrSQL,
current_date has time as well as date

SQL> set serverout on
SQL> begin
  2     dbms_output.put_line(to_char(current_date,'yyyy-mm-dd hh24:mi:ss'));
  3  end;
  4  /
2008-07-17 14:46:49
 
PL/SQL procedure successfully completed.
 
SQL>

Select allOpen in new window

SD,
   Thanks, I've always avoided it.  it was buggy early on.

See when the MIn = 0, It does not go into the if loop. So it directly updates the current_date value. And the same thing works in one server/DB and does not work on another server/db. Bothe the databases are on differnt servers.
Thanks,
Swami..

i avoid it because it reflects session settings
when I query sysdate, I'm generally looking for something that is user-independent.

what loop are you talking about?  That procedure has no loops in it.

The greatest(0,mins)  part ensures it only modifies the current_date if the "mins" parameter is greater than 0.  That's the functionality you want,  right?


Your procedure can be simplified to just this....

CREATE OR REPLACE PROCEDURE STN01.Upt_Sesn (SesnGuid IN VARCHAR2, Mins IN INT)
AS
BEGIN
    UPDATE TAB1
     SET TAB1_END_DTE = currrent_date + greatest(0,mins)/1440
   WHERE GUID_ID = SesnGuid;
END;
/

Select allOpen in new window

Thanks for all your help.

Whil trying this
CREATE OR REPLACE PROCEDURE STN01.Upt_Sesn (SesnGuid IN VARCHAR2, Mins IN INT)
AS
BEGIN
    UPDATE TAB1
     SET TAB1_END_DTE = currrent_date + greatest(0,mins)/1440
   WHERE GUID_ID = SesnGuid;
END;

I am getting the error expected DATE and encountered INT, I guess it is for the variable mins which is an integer.

Thaks,
Swami..

CREATE OR REPLACE PROCEDURE STN01.Upt_Sesn (SesnGuid IN VARCHAR2, Mins IN number)
AS
BEGIN
    UPDATE TAB1
     SET TAB1_END_DTE = currrent_date + greatest(0,mins)/1440
   WHERE GUID_ID = SesnGuid;
END;

when do you get that?  when you compile it? or when you execute it?

Hi SD,

It solved the issue but could you provide me what could be the issue ? and what is the use of 1440 here.
Thanks,
Swami...

Thanks SD. I could solve the issue and thaks to all who assisted me in reaching the solution. But still my question remains unsaswered why the stored proc in its previous for will work in one server and not in the other server.

Thanks,
Swami...

the reason it works on one server but not the other is probably the dateadd function is broke on one box but not the other.


date math is done in days.

1440 is the number of minutes in a day.

so

current_date + greatest(0,mins)/1440

returns the current_date plus the number minutes (MIN) you specify provided the MINS is greater than 0.

if you just did current_date + mins  you would be adding days, not minutes.