There are actually two special control characters that are used to store diary field entries in the database. Unicode value 0003 is the "END OF TEXT" control character and is used to separate individual entries in a diary field. Unicode value 0004 is the "END OF TRANSMISSION" control character and is used to separate the date/time (stored in epoch time as an integer) from the user name, and the user name from the actual text (see http://unicode.org/Public/UNIDATA/NamesList.txt). The entries are stored as:
TIMESTAMP<0004>USER<0004>TEXT<0003> So, it should be possible to split the diary field on these characters, then use another function to convert the epoch integer into a readable date/time value. HTH, Thomas ----- Original Message ----- From: "Pierson, Shawn" <[EMAIL PROTECTED]> Newsgroups: gmane.comp.crm.arsystem.general To: <[email protected]> Sent: Thursday, May 08, 2008 07:35 Subject: Re: Date/time format in MS SQL Server reports is not in a readable format (Epoch date). I apologize in advance for not having all the details, but we did this at a company I used to work for. What you want to do is look through the Work Log field and find a special character that occurs somewhere around the date field. I wish I could remember what it was, because I think it was something different than a carriage return. Anyway, we found that special character and were able to split up the date, the username, and the text of the Work Log. I wish I had kept that information, but it's been years since I had to do it. I think this was on version 5 of ARS. Shawn Pierson -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Jay Sytangco Sent: Thursday, May 08, 2008 4:24 AM To: [email protected] Subject: Date/time format in MS SQL Server reports is not in a readable format (Epoch date). Hi everyone, This issue is specifically for the Diary fields. When diary fields are displayed in MS SQL Server reports, is there a way that we can identify the date/time from the rest of the data and format it as Date/ Time format? Example Problem Log report: ------------------------------------------------------------------------ ----------------------------------------------------- Problem Log 1209364923 ramirec2 Ticket submitted with the following initial values: problem description = Testing for Status Bar in TT form problem area = ADM Outsourcing-Remedy-Application priority = Other assignee = Chidmark Ramirez notify by = Contact Profile support level = 2 business critical = No ETF/UOM = 0 1209364923 telalert TelAlert: AlertID=9770, Recipient/PIN=ramirec2, Alert Status = Accepted 1209365370 ramirec2 Testing only. System Note: Status was changed to Closed ------------------------------------------------------------------------ ------------------------------------------------------- Note: We have no problem converting date/time fields into date/time format - ONLY for date/time in Diary fields. Thanks in advance for your help. Jay ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" Private and confidential as detailed here: http://www.sug.com/disclaimers/default.htm#Mail . If you cannot access the link, please e-mail sender. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
