For billing's programmers....

Oracle XPert
Дата: 22.09.2004 13:33:45
CREATE OR REPLACE FUNCTION NUMTOCHAR (MNUM IN NUMBER) RETURN VARCHAR2 IS
TCHAR VARCHAR2(16);
MCHAR VARCHAR2(255);
BEGIN
TCHAR:=TO_CHAR(MNUM,'099999999999.99');
IF TO_NUMBER(SUBSTR(TCHAR,1,4)) <> 0 THEN
MCHAR:=MCHAR||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR,1,4)),'J'),'JSP')|| ' BILLION ';
END IF;
IF TO_NUMBER(SUBSTR(TCHAR,5,3)) <> 0 THEN
MCHAR:=MCHAR||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR,5,3)),'J'),'JSP')|| ' MILLION ';
END IF;
IF TO_NUMBER(SUBSTR(TCHAR,8,3)) <> 0 THEN
MCHAR:=MCHAR||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR,8,3)),'J'),'JSP')|| ' THOUSAND ';
END IF;
IF TO_NUMBER(SUBSTR(TCHAR,11,3)) <> 0 THEN
MCHAR:=MCHAR||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR,11,3)),'J'),'JSP')||' ';
END IF;
IF TO_NUMBER(SUBSTR(TCHAR,15,2)) <> 0 THEN
MCHAR:=MCHAR||'AND PAISA '||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR,15,2)),'J'),'JSP')||' ';
END IF;
IF MCHAR IS NOT NULL THEN
MCHAR:=MCHAR||'ONLY';
END IF;
RETURN MCHAR;
END NUMTOCHAR;
Oracle XPert
Дата: 22.09.2004 13:39:06
create or replace procedure date_test( date1 date, date2 date) is
DateLow date;
DateHigh date;
day1 date;
day2 date;
Fraction1 number;
Fraction2 number;
days number;
HHDec number; -- Hours (decimal)
HH number; -- Hours (integer)
MMDec number; -- Minutes (decimal)
MM number; -- Minutes (integer)
SSDec number; -- Seconds (deminal)
SS number; -- Seconds (integrer)
begin
-- Put the dates in order (important when dealing with the fractions
-- of days left over from the subtraction - see later).
DateLow := least( Date1, Date2 );
DateHigh := greatest( Date1, Date2 );
-- Find the midnight preceding date1
Day1 := Trunc( DateLow );
-- Find the midnight preceding date2
Day2 := Trunc( DateHigh );
-- Calculate the days between the two dates (subtracting two DATEs gives a NUMBER)
-- A function to calculate the number of days between the two dates would return
-- this value.
Days := Day2 - Day1 - 1;
-- Now, what about the fractions of days left?
Fraction1 := Date1 - Day1;
Fraction2 := Date2 - Day2;
-- Calculate the total HH (and fractions of HH) from the
-- fractions of the days left over
HHDec := ( ( 1 - Fraction1 ) + Fraction2 ) * 24;
-- This could be >1 day
if HHDec >= 24 then
HHDec := HHDec - 24;
Days := Days + 1;
end if;
HH := floor( HHDec );
-- Here, you have the correct number of days and HH between the two dates.
-- Now obtain the MM from the fractions of HH.
MMDec := ( HHDec - HH ) * 60;
MM := floor( MMDec );
-- And the SS...
SSDec := ( MMDec - MM ) * 60;
SS := floor( SSDec );
-- You could carry on from here to get the fractions of a second......
--
dbms_output.put_line( to_char( days ) || ' Days, '
|| to_char( HH ) || ' Hours, '
|| to_char( MM ) || ' Minutes, '
|| to_char( SS ) || ' Seconds. ' );
end;
Oracle XPert
Дата: 22.09.2004 13:41:04
set echo off
set verify off
set feed off
set pages 0
set lines 80

column week format a80

accept mon prompt 'Provide the month (0-12) : '
accept year prompt 'Provide the year (format YYYY) : '

select decode(marker, 1, '------ ' ||
substr(rtrim(ltrim('&year')), 1, 1) || ' ' ||
substr(rtrim(ltrim('&year')), 2, 1) || ' ' ||
substr(rtrim(ltrim('&year')), 3, 1) || ' ' ||
substr(rtrim(ltrim('&year')), 4, 1) || ' ' ||
'-----',
3, ' S M T W T F S',
2,
decode(mon, 1, ' J A N U A R Y ',
2, ' F E B R U A R Y ',
3, ' M A R C H ',
4, ' A P R I L ',
5, ' M A Y ',
6, ' J U N E ',
7, ' J U L Y ',
8, ' A U G U S T ',
9, ' S E P T E M B E R ',
10, ' O C T O B E R ',
11, ' N O V E M B E R ',
12, ' D E C E M B E R '),
4,
decode(weekday, '1', ' 1 2 3 4 5 6 7',
'2', ' 1 2 3 4 5 6',
'3', ' 1 2 3 4 5',
'4', ' 1 2 3 4',
'5', ' 1 2 3',
'6', ' 1 2',
'7', ' 1'),
32,
decode(mon, 2,
decode(lastday, '28',
decode(weekday, '1', '28 ',
'2', '27 28 ',
'3', '26 27 28 ',
'4', '25 26 27 28 ',
'5', '24 25 26 27 28 ',
'6', '23 24 25 26 27 28 ',
'7', '23 24 25 26 27 28 '),
'29',
decode(weekday, '1', '29 ',
'2', '28 29 ',
'3', '27 28 29 ',
'4', '26 27 28 29 ',
'5', '25 26 27 28 29 ',
'6', '24 25 26 27 28 29 ',
'7', '23 24 25 26 27 28 29')),
decode(lastday, '31',
decode(weekday, '1', '29 30 31 ',
'2', '28 29 30 31 ',
'3', '27 28 29 30 31 ',
'4', '26 27 28 29 30 31 ',
'5', '25 26 27 28 29 30 31',
'6', '24 25 26 27 28 29 30',
'7', '23 24 25 26 27 28 29'),
decode(weekday, '1', '29 30 ',
'2', '28 29 30 ',
'3', '27 28 29 30 ',
'4', '26 27 28 29 30 ',
'5', '25 26 27 28 29 30 ',
'6', '24 25 26 27 28 29 30',
'7', '23 24 25 26 27 28 29'))),
39,
decode(mon, 2, ' ',
decode(lastday, '31',
decode(weekday, '6', '31 ',
'7', '30 31 ',
' '),
'30',
decode(weekday, '7', '30 ',
' '))),
decode(weekday, '1', lpad(to_char(marker - 3), 2) || ' ' ||
lpad(to_char(marker - 2), 2) || ' ' ||
lpad(to_char(marker - 1), 2) || ' ' ||
lpad(to_char(marker), 2) || ' ' ||
lpad(to_char(marker + 1), 2) || ' ' ||
lpad(to_char(marker + 2), 2) || ' ' ||
lpad(to_char(marker + 3), 2),
'2', lpad(to_char(marker - 4), 2) || ' ' ||
lpad(to_char(marker - 3), 2) || ' ' ||
lpad(to_char(marker - 2), 2) || ' ' ||
lpad(to_char(marker - 1), 2) || ' ' ||
lpad(to_char(marker), 2) || ' ' ||
lpad(to_char(marker + 1), 2) || ' ' ||
lpad(to_char(marker + 2), 2),
'3', lpad(to_char(marker - 5), 2) || ' ' ||
lpad(to_char(marker - 4), 2) || ' ' ||
lpad(to_char(marker - 3), 2) || ' ' ||
lpad(to_char(marker - 2), 2) || ' ' ||
lpad(to_char(marker - 1), 2) || ' ' ||
lpad(to_char(marker), 2) || ' ' ||
lpad(to_char(marker + 1), 2),
'4', lpad(to_char(marker - 6), 2) || ' ' ||
lpad(to_char(marker - 5), 2) || ' ' ||
lpad(to_char(marker - 4), 2) || ' ' ||
lpad(to_char(marker - 3), 2) || ' ' ||
lpad(to_char(marker - 2), 2) || ' ' ||
lpad(to_char(marker - 1), 2) || ' ' ||
lpad(to_char(marker), 2),
'5', lpad(to_char(marker - 7), 2) || ' ' ||
lpad(to_char(marker - 6), 2) || ' ' ||
lpad(to_char(marker - 5), 2) || ' ' ||
lpad(to_char(marker - 4), 2) || ' ' ||
lpad(to_char(marker - 3), 2) || ' ' ||
lpad(to_char(marker - 2), 2) || ' ' ||
lpad(to_char(marker - 1), 2),
'6', lpad(to_char(marker - 8), 2) || ' ' ||
lpad(to_char(marker - 7), 2) || ' ' ||
lpad(to_char(marker - 6), 2) || ' ' ||
lpad(to_char(marker - 5), 2) || ' ' ||
lpad(to_char(marker - 4), 2) || ' ' ||
lpad(to_char(marker - 3), 2) || ' ' ||
lpad(to_char(marker - 2), 2),
'7', lpad(to_char(marker - 9), 2) || ' ' ||
lpad(to_char(marker - 8), 2) || ' ' ||
lpad(to_char(marker - 7), 2) || ' ' ||
lpad(to_char(marker - 6), 2) || ' ' ||
lpad(to_char(marker - 5), 2) || ' ' ||
lpad(to_char(marker - 4), 2) || ' ' ||
lpad(to_char(marker - 3), 2))) week
from
(select to_number('&mon') mon,
r marker,
to_char(to_date('01' ||
lpad('&mon', 2, '0') || '&year', 'DDMMYYYY'), 'D') weekday,
to_char(last_day(to_date('01' ||
lpad('&mon', 2, '0') || '&year', 'DDMMYYYY')), 'DD') lastday
from
(select r
from
(select rownum r
from all_objects
where rownum < 40)
where r in (1, 2, 3, 4, 11, 18, 25, 32, 39))
order by r)
/
Stax
Дата: 22.09.2004 13:58:04
:)
For NOT billing's programmers и чисел меньше 5373485
SQL> select to_char(to_date(5373484,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR

For NOT billing's programmers и разницы дат
select floor(((date1-date2)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600 -
       (floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
       || ' SECS ' time_difference
from
(select sysdate date1,sysdate-1.23 date2 from dual)
SQL> /
TIME_DIFFERENCE
--------------------------------
29 HOURS 31 MINUTES 12 SECS
Elic
Дата: 22.09.2004 14:01:09
А биллинг-програмистам вся эта херня мало поможет