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.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"
