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
Solved

Constraint on date column

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Records that don't match 8 50
any step by steps guide on how to install Oracle 12c on Windows 10 8 56
Email query results in HTML 6 29
add more rows to hierarchy 3 25
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

840 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