Dave, Try this:
Select Createdate = convert(char, DATEADD(s, convert(int, TRUI_Help_Desk.Created - 25000), '01-01-1970') Cheers! -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, January 09, 2007 12:08 PM To: [email protected] 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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
