Hi all
One of the developers came to me. They are using a Genesys call logging
system.
It has apparently stored a date time in a number field as the number of
seconds since 1970 1 Jan 0:00
They need to know exactly what time this is.
I can not off hand remember that oracle got a conversion
Hi all
One of the developers came to me. They are using a
Genesys call logging
system.
It has apparently stored a date time in a number
field as the number of
seconds since 1970 1 Jan 0:00
They need to know exactly what time this is.
I can not off hand remember that oracle got a
conversion
SELECT to_date('01-01-1970','DD-MM-') + no_of_seconds / (24*60*60) from
dual;
Regards
Naveen
-Original Message-
Sent: Thursday, January 23, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L
Hi all
One of the developers came to me. They are using a Genesys call logging
system.
Title: Implicit Date conversion problem
We have a query (please don't ask me why it is written this way)
--Doesn't work:
select ea1.ep_number, ea1.est_dt, veas.est_dt
from (select a.ep_number, ea.est_dt
from v_episode_avail_summary_break a, episode_airings ea
where a.ep_number
Title: RE: Implicit Date conversion problem
Well,
Let me point out one thing following is the original query ...
-- this returns no rows ...
select ea1.ep_number, ea1.est_dt, veas.est_dt
from (select a.ep_number, ea.est_dt
from v_episode_avail_summary_break a, episode_airings ea
What does doesn't work mean?
Wrong data?
Error message?
etc...??
Also the trunc(to_date('01-Jan-03','DD-Mon-YY')) is not quite right
most likely.
It should probably be:
trunc(to_date('01-Jan-03','DD-Mon-YY'))
to_date('01-Jan-03 23:59:59','DD-Mon-YY' HH24:MI:SS')
or if used with a
Title: RE: Implicit Date conversion problem
http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/sql_elements2a.htm#54201
Look up for table 2-10 and then below the table look for 4th bullet ... that's where I found it out ...
At-least Tom you know my
Title: Implicit Date conversion problem
Raj,
you
are forgetting how dates are stored internally vs. how they are displayed by
sqlplus.
change
your first query to:
select ea1.ep_number, ea1.est_dt,
veas.est_dt from (select a.ep_number, ea.est_dt
from
v_episode_avail_summary_break
Title: RE: Implicit Date conversion problem
Sorry about that ... I am working on a sev-1 problem with Oracle (ora-904 error if anyone is interested).
Doesn't work means it doesn't return any data the working query returns correct number of rows. the trunc() syntax is right ... est_dt
Raj:
The date column has a timestamp value as well. By using the TRUNC
function, you are getting all dates regardless of the time value (i.e.
12:01 AM, etc.) . When dealing with DATE datatypes it is always a good
practice to use the TRUNC function on the columns to prevent the time of
day
Title: RE: Implicit Date conversion problem
Raj,
you
said:
"according to Oracle, when comparing a
varchar2 to a date column, the string does get converted to a date ...
"
This
has got to be wrong. If you compare a varchar2 column with anumber,
Oracle try's to convert th
Jamadagni, Rajendra wrote:
Well,
Let me point out one thing following is the original query ...
-- this returns no rows ...
select ea1.ep_number, ea1.est_dt, veas.est_dt
from (select a.ep_number, ea.est_dt
from v_episode_avail_summary_break a, episode_airings ea
]
cc:
Subject:RE: Implicit Date conversion problem
Raj,
you said:
according to Oracle, when comparing a varchar2 to a date column, the
string does get converted to a date ...
This has got to be wrong. If you compare a varchar2 column with a number,
Oracle try's to convert
PROTECTED]
cc:
Subject:RE: Implicit Date conversion problem
Raj,
you said:
according to Oracle, when comparing a varchar2 to a date column, the
string does get converted to a date ...
This has got to be wrong. If you compare a varchar2 column with a number,
Oracle
14 matches
Mail list logo
