Converting Remedy Datetime for SQL Query
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.Cinsert 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.Cinsert field id IS NOT NULL and h.Cinsert 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
Re: Converting Remedy Datetime for SQL Query
This is what I have and I've never had a problem. dateadd(second, Create Date Field + 7200, '1 Jan 1970' Make sure that the number of seconds you add or subtract is correct according to your timezone. Regards, Basil Webster Remedy Developer Siemens Business Services (Pty) Ltd * E-Mail: [EMAIL PROTECTED] ( Tel: +27 11 652 7523 Ê Fax: +27 11 652-7501 ) Mobile: +27 82 452 9389 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of James Van Sickle Sent: 25 October 2006 19:52 To: arslist@ARSLIST.ORG Subject: Converting Remedy Datetime for SQL Query 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.Cinsert 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.Cinsert field id IS NOT NULL and h.Cinsert 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
Re: Converting Remedy Datetime for SQL Query
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/ 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/ hh24:mi:ss'), 'Europe/Zurich') - (timestamp '1970-01-01 00:00:00 GMT'))) +60*extract(minute from (from_tz(to_timestamp(txtdate,'dd/mm/ 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/ 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/ 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.Cinsert 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.Cinsert field id IS NOT NULL and h.Cinsert 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
