Home » SQL & PL/SQL » SQL & PL/SQL » Date Function (oracle 10g)
Date Function [message #296249] Fri, 25 January 2008 05:22 Go to next message
ramyashree_rp
Messages: 3
Registered: January 2008
Junior Member
How to assign day,month,year to three different variables for a given date
Re: Date Function [message #296250 is a reply to message #296249] Fri, 25 January 2008 05:28 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

do you mean like this
 var_day varchar2(3):=to_char(sysdate,'dy');


regards,
Re: Date Function [message #296251 is a reply to message #296250] Fri, 25 January 2008 05:37 Go to previous messageGo to next message
ramyashree_rp
Messages: 3
Registered: January 2008
Junior Member
yes.

i have written the following procedure:

CREATE OR REPLACE PROCEDURE P_DATE_MONTH(M_START_DATE DATE)
AS
DATE_FROM DATE;
DD_FROM VARCHAR2(100);
MON_FROM VARCHAR2(100);
YYYY_FROM VARCHAR2(100);

BEGIN
DATE_FROM := TO_DATE(M_START_DATE,'DD/MON/YYYY');
DD_FROM := to_char(M_START_DATE,'Day');
MON_FROM := to_char(M_START_DATE,'Month');
YYYY_FROM :=to_char(M_START_DATE,'Year') ;

DBMS_OUTPUT.PUT_LINE('DD_FROM :'||DD_FROM);
DBMS_OUTPUT.PUT_LINE('MON_FROM :'||MON_FROM);
DBMS_OUTPUT.PUT_LINE('YYYY_FROM :'||YYYY_FROM);

END P_DATE_MONTH;

this gives the following output:
DD_FROM :Monday
MON_FROM : January
YYYY_FROM :Two Thousand Seven

i want the output as follows
DD_FROM : 25 (the date)
MON_FROM : Jan
YYYY_FROM :2008
Re: Date Function [message #296252 is a reply to message #296251] Fri, 25 January 2008 05:46 Go to previous messageGo to next message
ramyashree_rp
Messages: 3
Registered: January 2008
Junior Member
i got the answer. anyway thanks for helping Smile
Re: Date Function [message #296253 is a reply to message #296251] Fri, 25 January 2008 05:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You used the TO_CHAR function and that's OK; but, you didn't use a correct format to produce a desired output. See which date format models Oracle offers.

P.S. I see you've found the answer; however, it would be nice if you shared it with the rest of the community.
Re: Date Function [message #296259 is a reply to message #296252] Fri, 25 January 2008 06:10 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Sybase to oracle conversion
Next Topic: multiple table trigger or constraint
Goto Forum:
  


Current Time: Sat Dec 10 20:17:52 CST 2016

Total time taken to generate the page: 0.09882 seconds