Hello,
I don't think you can use them (Oracle 9i is required and I understand
you're using MS SQL), but for the record here are a couple of
functions I wrote recently to convert remedy timestamps to/from a more
reasonable format.
Regards,
Carlos Ungil
create function timestamp_to_date
(remdate in number)
return char
is
stuff char(20);
begin
select to_char((timestamp '1970-01-01 00:00:00 GMT'+
numtodsinterval(remdate, 'SECOND')) at time zone
'Europe/Zurich','dd/mm/yyyy hh24:mi:ss')
into stuff
from dual;
return(stuff);
end timestamp_to_date;
create function date_to_timestamp
(txtdate in varchar2)
return number
is
stuff number;
begin
select extract(second from
(from_tz(to_timestamp(txtdate,'dd/mm/yyyy hh24:mi:ss'),
'Europe/Zurich') - (timestamp '1970-01-01 00:00:00 GMT')))
+60*extract(minute from (from_tz(to_timestamp(txtdate,'dd/mm/yyyy
hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00
GMT')))
+60*60*extract(hour from (from_tz(to_timestamp(txtdate,'dd/mm/yyyy
hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00
GMT')))
+24*60*60*extract(day from (from_tz(to_timestamp(txtdate,'dd/mm/yyyy
hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00
GMT')))
into stuff
from dual;
return(stuff);
end date_to_timestamp;
On 10/25/06, James Van Sickle <[EMAIL PROTECTED]> wrote:
Fellow ARSList Members
I need to convert the Epoch time format that Remedy stores its datetime
data into a more readable format for an SQL query. According to an older
KB article I have from Remedy, Remedy recommends using "DATEADD(day,
h.C<insert field id>-2440588, '01/01/1970')" to format what they store as
1161641694 into "10/23/2006 6:14:54 PM". The article also claims I have to
add "h.C<insert field id> IS NOT NULL" and "h.C<insert field id> > 2440590"
to the where statement of the query.
The following is the SQL query I am running based on the KB article.
select h.C1 as "Case ID",
DATEADD(day, h.C3-2440588, '01/01/1970') as "Arrival Time",
from T87 h
where h.C1 = 'HD367848'
and h.C3 IS NOT NULL
and h.C3 > 2440590
When I use this query, my Microsoft SQL Server 2000 returns "Adding a
value to a 'datetime' column caused overflow." Does anyone have the
correct string to use in a SQL query to convert the returned date/time
values into readable date/time format. I would appreciate any assistance
you can give on this, and thank you for you time regarding this question.
James Van Sickle
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers
Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers
Are"
