Home » SQL & PL/SQL » SQL & PL/SQL » date format
date format [message #280997] Thu, 15 November 2007 06:27 Go to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

hi guys

please help


i have the ff procedure


Create or replace procedure populate_year
(My_year in varchar)
IS
First_day date;
Last_day date;
Begin

First_day := trunc (TO_DATE (my_year, 'YYYY'),'YEAR');
Last_day := last_day(to_date(First_day),’dd-mon-yyyy’)
dbms_output.put_line (' first day '|| First_day );

End;
/


i need this procedure to return a first day of the year and the last day
from the procedure this line
Last_day := last_day(to_date(First_day),’dd-mon-yyyy’)
is not working

when i call populate _year(2006)

results are

SQL> call populate_year(2008);
first day 01-JAN-08

from this then i'll be able to loop from first to last day and populate my sales calender
Re: date format [message #281003 is a reply to message #280997] Thu, 15 November 2007 06:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Last_day is an Oracle built-in. You better change your variable name.

Besides, the code pasted by you (and please use code tags next time) won't compile. You miss a semicolon (;) in the line that "doesn't work".

MHE

[Updated on: Thu, 15 November 2007 06:40]

Report message to a moderator

Re: date format [message #281016 is a reply to message #280997] Thu, 15 November 2007 07:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And

Why to_date Function is applied on Last_day which is of DATE datatype ?

Even the Bracket '(' Mispalced ..


Last_day := last_day(to_date(First_day),'dd-mon-yyyy')

Thumbs Up
Rajuvan

[Updated on: Thu, 15 November 2007 07:07]

Report message to a moderator

Re: date format [message #281018 is a reply to message #280997] Thu, 15 November 2007 07:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You might have tried some thing like

Create or replace procedure populate_year
 (My_year in varchar)
 IS
 First_day_d date;
 Last_day_d date;
 Begin
 First_day_d := trunc (TO_DATE (my_year, 'YYYY'),'YEAR');
 Last_day_d := last_day(Add_months(First_day_d,11));
 dbms_output.put_line (' first day '|| First_day_d );
 dbms_output.put_line (' Last day '|| Last_day_d );
END;


Thumbs Up
Rajuvan.
Re: date format [message #281031 is a reply to message #281003] Thu, 15 November 2007 07:36 Go to previous messageGo to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

thanks guys

for all your quick responses, they are a real help!





Re: date format [message #281212 is a reply to message #281003] Fri, 16 November 2007 02:10 Go to previous messageGo to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

thanks guys


for all your quick reply

i'll contact you soon

from rebeccah
Re: date format [message #281223 is a reply to message #281031] Fri, 16 November 2007 03:15 Go to previous messageGo to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

hi please help

i have this procudure




SQL> Create or replace procedure populate_year
2 (My_year in varchar)
3 IS
4 vFirst_day date;
5 vLast_day date;
6 next_sat date;
7 Begin
8 vFirst_day := trunc (TO_DATE (my_year, 'YYYY'),'YEAR');
9 vLast_day := last_day(Add_months(vFirst_day,11));
10
11 If (TO_CHAR (vFirst_day,'day')='saturday') then
12
13
14 dbms_output.put_line ('yes it is a saturday'|| TO_CHAR (vFirst_day,'DAY'));
15 Else
16 next_sat:=next_day(vFirst_day,'sat');
17 dbms_output.put_line ('no '|| TO_CHAR (vFirst_day,'DAY'));
18
19 End if;
20 End populate_year;
21 /

Procedure created.


SQL> call populate_year(2005);
no SATURDAY


my expected results are

when the firstday of the year is not saturday then it should
display :no SATURDAY and the next first(saturday) of the year passed as the parameter

"i just dont know how to compare between dates"

thanks
rebeccah






when i compile it



Re: date format [message #281226 is a reply to message #281223] Fri, 16 November 2007 03:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Does this help?

SQL> SELECT DECODE(TO_CHAR(TO_DATE('1-1-1977', 'dd-mm-yyyy')
  2                      , 'dy'
  3                      , 'NLS_DATE_LANGUAGE=ENGLISH'
  4                       )
  5              , 'sat', 'Saturday'
  6              , 'No Saturday'
  7               )
  8  FROM   DUAL
  9  /

DECODE(T
--------
Saturday

SQL> SELECT DECODE(TO_CHAR(TO_DATE('1-1-1978', 'dd-mm-yyyy')
  2                      , 'dy'
  3                      , 'NLS_DATE_LANGUAGE=ENGLISH'
  4                       )
  5              , 'sat', 'Saturday'
  6              , 'No Saturday'
  7               )
  8  FROM   DUAL
  9  /

DECODE(TO_C
-----------
No Saturday

SQL> SELECT DECODE(TO_CHAR(TO_DATE('1-1-2008', 'dd-mm-yyyy')
  2                      , 'dy'
  3                      , 'NLS_DATE_LANGUAGE=ENGLISH'
  4                       )
  5              , 'sat', 'Saturday'
  6              , 'No Saturday'
  7               )
  8  FROM   DUAL
  9  /

DECODE(TO_C
-----------
No Saturday
have a look in the manuals for date format models.

MHE
Re: date format [message #281231 is a reply to message #281223] Fri, 16 November 2007 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And have a look at OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Fri, 16 November 2007 03:36]

Report message to a moderator

Re: date format [message #281246 is a reply to message #281226] Fri, 16 November 2007 03:56 Go to previous messageGo to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

No , what I want is

If (vfirst-day='sat') then
  Statements....
else
  mynext_Saturday:=next_day(first_day,'sat');
  statement.....
end if;

now the problem here is that i cannot get the function to compare even if the condition is met the program jump to the else statement(S)

kind Regards,


[EDITED by LF: message reformatted]

[Updated on: Fri, 16 November 2007 04:41] by Moderator

Report message to a moderator

Re: date format [message #281250 is a reply to message #281246] Fri, 16 November 2007 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should post more blank lines we can still read more than one line per page.

You could also post one word per line then you'll take more space.

Regards
Michel

[Updated on: Fri, 16 November 2007 04:11]

Report message to a moderator

Re: date format [message #281257 is a reply to message #281250] Fri, 16 November 2007 04:24 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel has a point. Try the preview button before you post.

I came up with this:
SQL> CREATE OR REPLACE PROCEDURE process_year( pin_year IN NUMBER )
  2  IS
  3    v_firstday VARCHAR2(5);
  4    v_next_sat DATE;
  5  BEGIN
  6    v_firstday := TO_CHAR( TRUNC(TO_DATE(pin_year,'YYYY'),'YEAR')
  7                         , 'dy','NLS_DATE_LANGUAGE=ENGLISH'
  8                         );
  9    IF v_firstday = 'sat' THEN
 10      dbms_output.put_line('first day was Saturday...');
 11    ELSE
 12      dbms_output.put_line('January first is not a Saturday');
 13      v_next_sat := NEXT_DAY(TRUNC(TO_DATE(pin_year,'YYYY'),'YEAR'),'SAT');
 14      dbms_output.put_line( 'The first Saturday is: '
 15                           ||to_char(v_next_sat,'DD/MM/YYYY')
 16                          );
 17    END IF;
 18  END;
 19  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL> EXEC process_year(1977);
first day was Saturday...

PL/SQL procedure successfully completed.

SQL>
SQL> EXEC process_year(1988);
January first is not a Saturday
The first Saturday is: 02/01/1988

PL/SQL procedure successfully completed.

SQL>
SQL> DROP PROCEDURE process_year
  2  /

Procedure dropped.
Is that what you want?

MHE
Previous Topic: How to user IF ELSE in Oracle 8i SQL?
Next Topic: Questions on searching specific rows
Goto Forum:
  


Current Time: Sun Dec 04 00:40:13 CST 2016

Total time taken to generate the page: 0.09373 seconds