| date format [message #280997] |
Thu, 15 November 2007 06:27  |
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   |
 |
Maaher
Messages: 7065 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 #281226 is a reply to message #281223] |
Fri, 16 November 2007 03:26   |
 |
Maaher
Messages: 7065 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   |
 |
Michel Cadot
Messages: 68770 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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   |
 |
Michel Cadot
Messages: 68770 Registered: March 2007 Location: Saint-Maur, France, https...
|
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  |
 |
Maaher
Messages: 7065 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
|
|
|
|