Thanks Shawn and Thomas for your ideas. I'll try to apply them in MS SQL.
Regards, Jay Sytangco CAI Outsourcing IT Equipment and Process Team Phone: 63.2.5819240 Email: [EMAIL PROTECTED] ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Bean Sent: Thursday, May 08, 2008 10:34 PM To: [email protected] Subject: Re: Date/time format in MS SQL Server reports is not in a readable format (Epoch date). ** Although I haven't actually seen a function to do this in MS-SQL, there is a thread in the ARSList archives from September 2006 under the subject heading "Date Field/PHP conversion" (author: Bob Palma) that shows how to convert diary fields in PHP: # $worklog is set to value of diary field from the DB $array_of_diary_entries = explode('', $worklog); foreach ($array_of_diary_entries as $i) { $array_of_entry_parts = explode('', $i); $date = date('m/j/y g:i:s a', $array_of_entry_parts[0]); if ($array_of_entry_parts[1] != "") { print "Date -- $date <br>\n"; print "User -- {$array_of_entry_parts[1]} <br>\n"; print "Diary Entry -- {$array_of_entry_parts[2]} <br>\n"; } } This is basically splitting the individual elements of the diary field (i.e., timestamps, user names, text) into an array, using the special control characters ('' is char 03, '' is char 04), then it is converting the epoch integer value into a readable date and printing the result. I'm guessing the approach would be similar in MS-SQL. --Thomas ----- Original Message ----- From: Thomas Bean <mailto:[EMAIL PROTECTED]> Newsgroups: gmane.comp.crm.arsystem.general To: [email protected] Sent: Thursday, May 08, 2008 8:34 AM Subject: Re: Date/time format in MS SQL Server reports is not in a readable format (Epoch date). ** 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] <mailto:[EMAIL PROTECTED]> > Newsgroups: gmane.comp.crm.arsystem.general To: <[email protected] <mailto:[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] <mailto:[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 <http://www.arslist.org> Platinum Sponsor: www.rmsportal.com <http://www.rmsportal.com> ARSlist: "Where the Answers Are" Private and confidential as detailed here: http://www.sug.com/disclaimers/default.htm#Mail <http://www.sug.com/disclaimers/default.htm#Mail> . If you cannot access the link, please e-mail sender. __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"

