Solved

Constraint on date column

Posted on 2011-09-14
12
370 Views
Last Modified: 2012-05-12
How can I create a date column to have values of the format ‘DD/MM/YYYY’.
The column have the data type DATE.
Similarly I have to create another column which should have values of the format ‘HH:MM’
Can it be achieve without necessarily changing the datatype of teh column.
DB: Oracle 10
0
Comment
Question by:diteps06
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 138 total points
ID: 36536482
Dates are not stored in any 'format'.  That are an internal numeric type format as date/time.

Why do you need a 'string' format for date and time?

If/when you migrate to 11g there is a virtual column that can easily get you want you need without the overhead of storing additional data.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 62 total points
ID: 36536754
It looks like you want to separate date into two. First should contain actual date and another column contain time.
Oracle provided the function to_char() function to convert date into desired result. i.e.

select to_char(sysdate, 'dd/mm/yyyy') Dt, to_char(sysdate,'hh24:mi') tM from dual;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36536895
>>to convert date into desired result

You can store the values as strings but I'm against doing this unless there is absolutely NO WAY to not do it and I've not come across any system/requirement to do it.

When people do implement this they always end up converting the stored 'strings' back into dates at query time.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 50 total points
ID: 36536896
I would store the date and time in one column with DATE datatype.

If you want to see it in a specific format, create a view over the table that forces the output format that you want.

By using strings, you have to do all the date validation yourself, let the database do the work.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36537008
I am sorry, I didn't mean to create two columns, but I am suggesting how two columns can be created dynamically if required in a query.
0
 
LVL 1

Author Comment

by:diteps06
ID: 36538427
In fact I wanted to avoid to use a sting column to store the date values. However, if I have to achive it the column has to be String data type then I do a check contraint on it using the oracle function to_char()
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36538573
You never answered my question:
Why do you need a 'string' format for date and time?

>>String data type then I do a check contraint on it using the oracle function to_char()

I'm not understanding what you are wanting to do.  validate the 'string' is a valid date?
0
 
LVL 1

Author Comment

by:diteps06
ID: 36539354
Why do you need a 'string' format for date and time?

To be able to satisfy the request that a date column should have values in the format 'dd/mm/yyyy'
All values in the column should be in the format 'dd/mm/yyyy'
So far it seems you cann't do it if the data type of the column is DATE.

I'm not understanding what you are wanting to do.  validate the 'string' is a valid date?

It is not actually to check if it is a date but to verify if it is in the date format 'dd/mm/yyyy'.
It is easier to do it if the datatype of the column is STRING.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36539410
>>date column should have values in the format 'dd/mm/yyyy'


Typically you convert dates to a string at query time.

You can store the date and time in a single date column and use to_char when you 'display' it using the examples posted by virdi_ds.

>>to verify if it is in the date format 'dd/mm/yyyy'.

So 65/32/9876 would be 'valid'?
0
 
LVL 1

Author Comment

by:diteps06
ID: 36540770

You are right it is advisable the column be in a DATE format. The convertion in the appropriate format should be done at queries or application level.
The requirement seems to be complementary to do at the table definition level
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 138 total points
ID: 36542465
It sounds like you have a requirement to create two new columns.

Can you possibly create a view off the base table with the to_char columns?

I'm sill unclear on your constraint requirement per my question in http:#a36539410

Want to remind you of something I mentioned earlier for when you migrate to 11g: virtual columns.
drop table tab1 purge;
create table tab1(col1 date);

insert into tab1 values(sysdate);
insert into tab1 values(sysdate-.4325);
commit;


create or replace view tab1_vw as select to_char(col1,'DD/MM/YYYY') string_date, to_char(col1,'HH24:MI') string_time from tab1;

select * from tab1;
select * from tab1_vw;

Open in new window

0
 
LVL 1

Author Comment

by:diteps06
ID: 36542790
TRUNC on the date column wolud it.

It'S just necessary to create A check constraint:

ALTER TABLE TAB ADD (
CONTRAINT
CHECK (FM_DAT = TRUNC(FM_DAT))
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now