FORMAT DATE TO PICK MM-DD-YYYY

i HAVE A DATE TRIGGER UPDATING THE DATE  COLUMN TO SYSDATE

I DO NOT WANT TIME IN THAT COLUMN

SO WHEN I DO
 TO_DATE(SYSDATE,'MM-DD-YYYY')


IT DOES NOT LIKE IT.


DECLARE  date1 DATE := sysdate;
BEGIN
  :new.RN_EXECUTION_DATE :=TO_DATE(SYSDATE,'MM-DD-YYYY');
   
END;


PLS HELP...THIS IS VVV URGENT
eileenbanikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You will always have a time part of an oracle date column.  If you always want it to be zero'd out:

trunc(sysdate)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eileenbanikAuthor Commented:
so should the statement be  
 :new.RN_EXECUTION_DATE :=trunc(SYSDATE);
0
slightwv (䄆 Netminder) Commented:
That should work (as should the original post).

If you are still receiving errors:  Please post the trigger and the error message you are receiving (DB version would also help).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KurtRCommented:
no need to use a format-mask.
use trunc(sysdate) instead.

you can easily test it:
-- fully qualified timestamp
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

-- only the date
select to_char(trunc(sysdate),'dd-mm-yyyy hh24:mi:ss') from dual;

your code should be:
DECLARE  date1 DATE := sysdate;
BEGIN
  :new.RN_EXECUTION_DATE := trunc(SYSDATE);
END;
0
fadeshadowCommented:
Please, be advised that the trunc will also record the time, but it will be 00:00:00.
If you do not want the time to be displayed when selecting rows do

select  to_char( <date_field> , 'MM/DD/YYYY')
from <table_name>


SQL>select sysdate, trunc(sysdate) from dual;

SYSDATE            TRUNC(SYSDATE)
--------------        --------------
22/11/04 15:06   22/11/04 00:00

hope it helps
0
tangocoderCommented:
Hi, it depends on which version of Oracle you are running.
I use Oracle 9.0.1 on Windows 2000.

I wrote the query select to_char(trunc(sysdate),'DD-MON-YYYY') from dual and I got the date with the year 2004.  When I use to_date(trunc(sysdate),'DD-MON-YYYY') I got only 04 instead of 2004.

If you are using this in a cursor or for a cursor variable you can always convert the date field in the query using the to_char function with any format you want.
0
Mark GeerlingsDatabase AdministratorCommented:
Don't use "to_date" with sysdate, since sysdate is already a date.  You can use "to_char" with sysdate if you want to exclude the time portion when you retrieve it, or use "trunc" with sysdate to strip off the time portion.
0
kumaran100Commented:
Hi da,
The best way is as said above use of  TRUNC Function. It sill always truncate the time part from the date.
.................
BEGIN
  :new.RN_EXECUTION_DATE := TRUNC(SYSDATE);
END;

This is the standard we use in our project to truncate the time part from the date.

rdgs,
kums
0
eileenbanikAuthor Commented:
To,
slightwv, KurtR,fadeshadow,tangocoder,markgeer,kumaran100


All of your code and suggestions work and resolve my issue.All the answers are more than excellent.Thanks a Lot.for all your help..You are awesome.Eileen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.