• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Constraint on date column

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
diteps06
Asked:
diteps06
  • 5
  • 4
  • 2
  • +1
4 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
Technology Partners: 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!

 
johnsoneSenior Oracle DBACommented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
diteps06Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
diteps06Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
diteps06Author Commented:

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
 
slightwv (䄆 Netminder) Commented:
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
 
diteps06Author Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now