Solved

Constraint on date column

Posted on 2011-09-14
12
363 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

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

758 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

20 Experts available now in Live!

Get 1:1 Help Now