PL/SQL Function

[nutmeg]

PL/SQL Function
--------------------------------------------------------------------------------

I have to populate a time table with 2 years say 2000 to 2001 dates with the following requirements

Table Time
(TIME_ID: NUMBER(6) NOT NULL
DT: DATE NOT NULL
DAY_OF_WK: NUMBER(1) NULL
DAY_NUM_MO: NUMBER(2) NULL
DAY_NUM_YR: NUMBER(3) NULL
MO_NM: VARCHAR2(10) NULL
QTR_NUM: NUMBER(1) NULL
YR: NUMBER(4) NULL)

DT is Date
DAY_OF_WEEK: This column gives us the numerical representation of the day of the week (Sunday = 1, Monday = 2, etc). Like isWeekday, you will want to make adjustments if your DATEFIRST setting does NOT consider Sunday to be the first day of the week.
DAY_NUM_MO:This column gives us the numerical representation of the month (January = 1, February = 2, etc). We can use this, for example, when we're looking for a cross-section of all events that have happened in March, regardless of year.
MO_NM: Name of the Month Jan,Feb,Mar,etc
QTR_NUM: every 3 months is a quarter.
YR: either its 2000 or 2001 year.

Any Suggestions how to go abt this? in PL/SQL function or a procedure to insert data. I know there is a way to do in SQL server but I need it for Oracle.

--------------------------------------------------------------------------------

[nutmeg]

There are direct functions available for each of these in oracle, jus use the table
Format Mask Description

SCC or CC The century. If the SCC format is used BC dates are prefaced with a negative sign.
SYYYY or YYYY The four-digit year. If the SYYYY format is used BC dates are prefaced with a negative sign.
IYYY The four-digit ISO standard year.
YYY or YY or Y The last three, two, or one digits of the year.
IYY or IY or I The last three, two, or one digits of the ISO year.
Y.YYY The four-digit year with a comma.
YEAR or SYEAR
Year or SYear The year spelled out in uppercase or title case. The S prefix places a negative sign in front
of B.C. dates. The language is always English.
BC or AD The BC or AD indicator, without periods.
B.C. or A.D. The BC or AD indicator, with periods.
Q The quarter of the year from 1 through 4.
MM The number of the month in the year, from 01 through 12.
RM The Roman numeral representation of the month number.
MONTH or Month The name of the month in uppercase or title case in the language set by NLS_LANGUAGE
MON or Mon The abbreviated name of the month in uppercase or title case in the language set by NLS_LANGUAGE
WW The week in the year, from 1 through 53.
IW The ISO week in the year, from 1 through 52 or 53.
W The week in the month, from 1 through 5.
DDD The day in the year, from 1 through 366.
DD The day in the month, from 1 through 31.
D The day in the week, from 1 through 7. The starting day is determined by NLS_TERRITORY.
DAY or Day The name of the day in uppercase or title case format.
DY The 3 character abbreviated name of the day in the language set by NLS_LANGUAGE.
J The Julian day of the date; i.e., the number of days since 1 January 4712 BC
AM or PM The meridian indicator without periods.
A.M. or P.M. The meridian indicator with periods.
HH or HH12 The hour in the day, from 1 through 12.
HH24 The hour in the day, from 0 through 23.
MI The minutes component of the date/time, from 0 through 59.
SS The seconds component of the date/time, from 0 through 59.
sssss The number of seconds since midnight of the time component.
TH Suffix that converts a number to its ordinal format. The language is always English.
SP Suffix that converts a number to its spelled format. The language is always English.
SPTH Suffix that converts a number to its spelled and ordinal format. The language is always English.
Other text Any text in quotes will be reproduced in the formatted output of the conversion.
Date Format Models