?
Solved

FORMAT DATE TO PICK MM-DD-YYYY

Posted on 2004-11-22
9
Medium Priority
?
1,281 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:eileenbanik
9 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 12646478
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
 

Author Comment

by:eileenbanik
ID: 12646617
so should the statement be  
 :new.RN_EXECUTION_DATE :=trunc(SYSDATE);
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12647000
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Expert Comment

by:KurtR
ID: 12647433
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
 
LVL 1

Expert Comment

by:fadeshadow
ID: 12647510
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
 
LVL 1

Expert Comment

by:tangocoder
ID: 12648860
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12649603
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
 
LVL 1

Expert Comment

by:kumaran100
ID: 12654304
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
 

Author Comment

by:eileenbanik
ID: 12657709
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question