Date format in oracle/sql 1

[doti]

Hi,
I have a small problem with date format while creating a table under oracle sql.
I want to create a table in which one of the columns will be the starting time of the movie f.e 17:30;

I typed:
create table Movie_tbl(
Movie_Id number(2),
Title char(20),
Time date);

and then I wanted to insert data:

insert into Movie_tbl
values (1, 'National Treasure', '17:30');

but it gives me back that: invalid mounth

in what way should i define the time column????

Thanks in advance..
Doti

 

[SantaMufasa]

Doti,

Change your INSERT statement to read:

insert into Movie_tbl
values (1, 'National Treasure', to_date('17:30','hh24:mi'));

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[doti]

Unfortunatelly, it doesn't work.

In my database I have entries for time which are to be of the format hh24:mi
but i also have entries for date - yyyy-mm-dd

when i try to set the format of date f.e
alert session set NLS_DATE_FORMAT='hh24:mi';

it changes all the entries in my data base to yyyy-mm-dd

I have a problem to set it in the way to have both formats: yyyy-mm-dd and hh24:mi

Can you help me in some way....

Doti

 

[SantaMufasa]

Doti,

Just use the appropriate format in your SQL code at the appropriate time you need it. Every Date/Time expression in Oracle has both date and time components, even if you only enter time. Therefore, you use the appropriate format when you need it in your code...Don't change your NLS_DATE_FORMAT just for your time expressions.



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[doti]

ok, but I changed the NLS_DATE_FORMAT a few times already

My current settings are:

NLS_DATE_FORMAT='dd/mm/yy hh24:mi'

I created a table:

create table TimeTable(
event_id number(2) constraint e_pk primary key,
cinema char(20),
title char(20),
time date,
date_ date,
income number(4));


then i wanted to insert data:

insert into TimeTable
values(1,'SilverScreen','Solaris',to_date('17:30','hh24:mi'),to_date('23/02/03','dd/mm/yy'),1000);

but, unfortunatelly, the output is:

time : 01/03/06 17:30
date_: 23/02/03 00:00

any ideas what i made wrong????

 

[psilons]

Here's the excerpt from the book:
If you specify a date value without the time component the default time is 12AM. If you specify a date value without the date component the default value is the FIRST DAY OF THE CURRENT MONTH.

So, the date on the TIME column is seems to be correct. Even though, it may not be what you wanted to see there. You may just have to just insert today's date on the TIME column. Then again, I don't see why you would need two columns to hold same data. May be you can modify SELECT stmt to extract time and data from same column.

 

[doti]

I don't need two columnt to hold the same date. what i want is to have:

two columns like this:

Time Date_
17:30 20/03/05

can i do it in some simple way without modifying the select statement????

 

[SantaMufasa]

Doti,

Try this:

SQL> select to_char(sysdate,'hh24:mi')Time,to_char(sysdate,'dd/mm/yy')Date_ from dual;

TIME  DATE_
----- --------
10:44 18/03/05

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[natachatecgraf]

SantaMufasa:

I am facing the same situation described on the thread
number 181-27567. Unfortunately the solution you have
suggested :

select to_char(sysdate,'hh24:mi')Time,to_char(sysdate,'dd/mm/yy')Date_ from dual;

does *not* solve my problem as I have no way to know , before hand, if the column I'm dealing with , I mean reading from, is a full date column or the case I have now with a time only info.

please give me some help .... Do you know if there are any way to set and read the data format?

 

[SantaMufasa]

I am facing the same situation described on the thread number 181-27567.

Are you running into your problem in Oracle or in SQL Server? If you are using Oracle, then remember that any and every column that you define as DATE, whether you store just the DATE component or just a TIME component, always contains both DATE and TIME.


So, before we proceed, please confirm your operational venue.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[natachatecgraf]

I am working in ORACLE. Because the datetime always have the parts of the time and the date, I ask: There are any way to know which format was used to insert the data?

 

[SantaMufasa]

Natachatecgraf,

No...once a DATE column has data in it, it is impossible to determine what input format mask was responsible for the value.

On the other hand, if your DATE-or-TIME values follow a recurring pattern (such as "00:00:00" time values mean that the expression contains a DATE-only value and "01/01/0001" date values mean that the expression contains a TIME-only value) then you can evaluate the contents of a DATE expression and determine which type of DATE/TIME classification it is.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.