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"

Reply via email to