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