Home » SQL & PL/SQL » SQL & PL/SQL » DATE help (adding 7 hours to a date)
DATE help (adding 7 hours to a date) [message #210162] Tue, 19 December 2006 13:34 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have a date in our database that is stored as a ten digits number (7557285500) and the datatype is number. I need to add seven hours
to this date and do calculations with it. I've tried the following:

(pr.perform_date+7/24) but the output is 7557285500.2916666. What am I doing wrong?

Thanks,
Stan
Re: DATE help (adding 7 hours to a date) [message #210166 is a reply to message #210162] Tue, 19 December 2006 15:03 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
What you have looks like a Unix time stamp. Time stamps in UNIX are stored as an integer value representing the number of seconds since 1 January 1970. So to add 7 hours, simply add 25200 (60*60*7) to the timestamp

[Updated on: Tue, 19 December 2006 15:04]

Report message to a moderator

Re: DATE help (adding 7 hours to a date) [message #210169 is a reply to message #210162] Tue, 19 December 2006 15:17 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
When I try your suggestion, it adds 2 hours and 13 minutes.

Stan

[Updated on: Tue, 19 December 2006 15:24]

Report message to a moderator

Re: DATE help (adding 7 hours to a date) [message #210175 is a reply to message #210162] Tue, 19 December 2006 15:51 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
In UNIX time, 7557285500 would correspond to Sun, 25 Jun 2209 13:58:20.

Is that similar to what you were expecting? If not, do you know what the number in the column is supposed to represent?
Re: DATE help (adding 7 hours to a date) [message #210178 is a reply to message #210162] Tue, 19 December 2006 15:56 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Number as date ! Yuck !!

Now tell us what would be your number for these two dates.

te2mgphm>select sysdate,sysdate+numtodsinterval(7,'HOUR') from dual;

SYSDATE SYSDATE+NUMTODSINTE
------------------- -------------------
2006-12-19 14:52:58 2006-12-19 21:52:58

Once we have those two "numbers" we can figure out a way.


Srini

Re: DATE help (adding 7 hours to a date) [message #210186 is a reply to message #210178] Tue, 19 December 2006 16:22 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
7557285500 represents 11/29/2006 23:45 and I want to add 7 hours to this. I've tried (pr.perform_date+7/24) but the output is 7557285500.2916666. I've tried (pr.perform_date+25200) and this only adds 2 hours and 13 minutes.

Thanks for looking at this guys,
Stan

[Updated on: Tue, 19 December 2006 17:13]

Report message to a moderator

Re: DATE help (adding 7 hours to a date) [message #210187 is a reply to message #210186] Tue, 19 December 2006 16:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Well should we just guess how 7557285500 is converted to 11/30/2006 23:45 ? If you expect help then why don't you provide all the pieces to the puzzle ??

Re: DATE help (adding 7 hours to a date) [message #210189 is a reply to message #210187] Tue, 19 December 2006 16:41 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member

ddt.tochar(pr.perform_date,'mm/dd/yyyy hh24:mi')

Re: DATE help (adding 7 hours to a date) [message #210190 is a reply to message #210189] Tue, 19 December 2006 16:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select to_date('7557285500','mm/dd/yyyy hh24:mi') from dual;
select to_date('7557285500','mm/dd/yyyy hh24:mi') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month
Re: DATE help (adding 7 hours to a date) [message #210192 is a reply to message #210190] Tue, 19 December 2006 17:12 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
What is you point ebrian!

I didn't use
'to_date' in my example (so that you wouldn't have to guess, remember!!!!). Try using the pieces of the puzzle provided!

I used:

ddt.tochar(pr.perform_date,'mm/dd/yyyy hh24:mi') and it returns

11/29/2006 23:45


so type this in:

select
ddt.tochar(7557285500,'mm/dd/yyyy hh24:mi')
from
dual



Re: DATE help (adding 7 hours to a date) [message #210194 is a reply to message #210192] Tue, 19 December 2006 17:15 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
My point is you are providing us a black box !!!

SQL> select
  2  ddt.tochar(7557285500,'mm/dd/yyyy hh24:mi')
  3  from 
  4  dual;
ddt.tochar(7557285500,'mm/dd/yyyy hh24:mi')
*
ERROR at line 2:
ORA-00904: "DDT"."TOCHAR": invalid identifier
Re: DATE help (adding 7 hours to a date) [message #210196 is a reply to message #210194] Tue, 19 December 2006 17:23 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm not sure why it's not working for you.

select
   ddt.tochar(7557285500,'mm/dd/yyyy hh24:mi')
from
dual

returns:  11/29/2006 23:45
and

select
   ddt.tochar(7557285500+25200,'mm/dd/yyyy hh24:mi')
from
dual

returns:  11/30/2006 02:58 (2hrs and 13 mintues later, not the desired 7 hours later)


what else do you want?
Re: DATE help (adding 7 hours to a date) [message #210198 is a reply to message #210196] Tue, 19 December 2006 17:26 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The ddt.tochar is a function that you or someone on your project has developed in your database. We don't know what it consists of, therefore, we don't know how it's converting the number to a date ?
Re: DATE help (adding 7 hours to a date) [message #210200 is a reply to message #210198] Tue, 19 December 2006 17:29 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks for looking.

Stan
Re: DATE help (adding 7 hours to a date) [message #210201 is a reply to message #210162] Tue, 19 December 2006 17:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>what else do you want?
The source code for the function ddt.tochar
Re: DATE help (adding 7 hours to a date) [message #210202 is a reply to message #210201] Tue, 19 December 2006 17:54 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I guess that might help me figure out how the conversion is being done and to extrapolate from that how to add 7 hours to the desired date.

Thanks,
Stan
Re: DATE help (adding 7 hours to a date) [message #210203 is a reply to message #210162] Tue, 19 December 2006 17:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I guess that might help me figure out how the conversion is being done and to extrapolate from that how to add 7 hours to the desired date.
Brilliant! Now why did not I think of that?
YOYO!
Re: DATE help (adding 7 hours to a date) [message #210204 is a reply to message #210203] Tue, 19 December 2006 18:10 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks guys!

[Updated on: Tue, 19 December 2006 18:14]

Report message to a moderator

Previous Topic: Query Tuning
Next Topic: UPDATE ERRO ORA-00933
Goto Forum:
  


Current Time: Sat Dec 03 22:03:06 CST 2016

Total time taken to generate the page: 0.08802 seconds