creating views of date portions

[posiedon]

Hi

using Oracle 8.1.7.2

I wish to create a series of views that contain date portions in order to populate drop down boxes.

I wish to create seperate views of:
Days
Months
Years
Hours
Minutes

I have a table that is loaded with data every night, this table contains date entries in the format:
DD:MON:YYYY:HH24:MI:SS

how can i create views of each section of the date entry?
I wish to do this so that (especially for the years) i have a current list of the possible date portions from which a user can create a new date entry for a search of the table containing the data.

Thanks

Stephen

 

[ThomVF]

Each view is created using the following structure:
create or replace view <view_name> as
<view select statement>;

Specific View Select statements:

Years:
select distinct to_char('YYYY',<date_field>) from <table>;
Months:
select distinct rtrim(to_char('MONTH',<date_field>)) from <table>;
Days:
select distinct rtrim(to_char('DAY',<date_field>)) from <table>;
Hours:
select distinct to_char('HH24',<date_field>) from <table>;
Minutes:
select distinct to_char('MI',<date_field>) from <table>;

Tom

 

[carp]

Tom's on the right track but he has the syntax backwards.
For instance, to_char('YYYY',<date_field>) should actually be to_char(<date_field>,'YYYY').

 

[ThomVF]

Ooops-Thanks Carp Doh!