Dave,
I think what you would need is to create a function to extract the unix
timestamps from the diary field, then convert them to date/time. Diary
fields are formatted as follows at the db level:
<unix timestamp>
control char(0004)
<user name>
control char(0004)
<entry text>
control char(0003)
<unix timestamp>
control char(0004)
<user name>
control char(0004)
<entry text>
control char(0003)
... etc. ...
So, in theory, you should be able to split the diary field text into an
array of its individual entries by using the "End of Text" control character
delimiter (0003), then parse out the individual elements of each entry by
using the "End of Transmission" control character delimiter (0004).
The first element of each entry would be the unix timestamp value, which you
would need to convert to a readable date/time format using the DATEADD
function (as in your example with the 'Createdate' value) or another custom
UDF that also accounts for time zone and DST considerations (many such
examples have been previously posted to the list).
After converting the unix timestamp to a readable date/time value, your
function would then need to concatenate the diary field elements back
together (replacing the control characters with returns) to display the
entire field contents. There should be a single return after each user name
element and a double return after each diary entry text element before the
following unix timestamp value.
If it works as described, the function should take the following value from
the database for the diary field:
969964287 fords Ticket unable to be escalated to US 969964572 fords
Ticket # B00010883 was logged initially at Intl L3 Tech.
...and display it something like the following:
2000-09-26 05:31:27 fords
Ticket unable to be escalated to US
2000-09-26 05:36:12 fords
Ticket # B00010883 was logged initially at Intl L3 Tech.
I haven't created such a function in MS-SQL, but I think it should be
possible. I have seen one similar example posted to the list back in
September that showed a conversion method for PHP (From: Bob Palma, Subject:
Re: Date Field/PHP conversion, Date: 2006-09-26).
I hope this at least helps to point you in the right direction...
Kind regards,
Thomas
----- Original Message -----
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
Newsgroups: gmane.comp.crm.arsystem.general
Sent: Tuesday, January 09, 2007 14:08
Subject: Date Conversion in Diary Field
Hi,
I have a customer that wants to run reports using direct SQL rather than
using Crystal or the Remedy ODBC driver. In doing this he found that the
date\time stamp within the diary field is returned as Unix time. He ld like
to convert this timestamp in the diary field into the normal dd/mm/yyyy
time format.
The select statement is as follows:
> SELECT DATEADD(second, TRUI_Help_Desk.Created, 'Jan 1, 1970') AS
> Createdate, TRUI_Help_Desk.Status, TRUI_Help_Desk.Problem_Type,
> TRUI_Help_Desk.Country,
> TRUI_Help_Desk.Item_Affected, TRUI_Help_Desk.Group_Assigned,
> TRUI_Help_Desk.Assigned_To,
> TRUI_Help_Desk.Severity_Level,
> TRUI_Help_Desk.Login_Country_H, TRUI_Help_Desk.Problem_Description,
> TRUI_Help_Desk.Problem_Category,
> TRUI_Help_Desk.Request_ID,
> TRUI_Help_Desk.Problem_Log, TRUI_Help_Desk.Created,
> field_enum_values.value
> FROM TRUI_Help_Desk INNER JOIN
> field_enum_values ON TRUI_Help_Desk.Status =
> field_enum_values.enumId AND TRUI_Help_Desk.Status =
> field_enum_values.enumId
> WHERE (TRUI_Help_Desk.Request_ID = @Request) AND
> (field_enum_values.schemaId = 174) AND (field_enum_values.fieldId = 7)
with the results of:
> <<Remedy%5FCurrent%5FTicket[1].pdf>>
> 969964287 fords Ticket unable to be escalated to
> US 969964572 fords Ticket # B00010883 was logged initially at Intl L3
> Tech.
Thanks...Dave
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
