?
Solved

Constraint on date column

Posted on 2011-09-14
12
Medium Priority
?
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 552 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 248 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 77

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 200 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
 
LVL 77

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 77

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 552 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

771 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