Converting a BMC Remedy timestamp to mm/dd/yyyy format

According to the documentation I've found from AR Systems on BMC Remedy, timestamps in an MSSQL Server environment (we're using开发者_开发问答 SQL Server 2005 here) are stored as an int datatype in UNIX timestamp format.

I cannot for the life of me get custom SQL in Business Objects Web Intelligence to let me convert this timestamp into mm/dd/yyyy format, which I need to be able to group records by a date (without the timestamp, obviously). Anything I try to do involving math or datatype conversion throws an "invalid type" error. I can't convert the field to an int, varchar, datetime, or anything else. The only function that works is DATEADD, which still returns the full timestamp.

How can I get this converted? I'm going nuts here!

to convert GMT/UTC/Zulu to Local time Zone(EDT/New York):

DATEADD(hour,-5,DATEADD(s,Last_Modified_Date,'1/1/1970 00:00:00'))

Example of use to display Remedy work info entries (Transact-SQL):

SELECT Work_Log_ID, DATEADD(hour, +12, DATEADD(s, Last_Modified_Date, '1/1/1970 00:00:00')) as Last_Modified_Date , Description, Detailed_Description, Infrastructure_change_ID, Number_of_Attachments 
FROM dbo.CHG_WorkLog
  WHERE Infrastructure_Change_ID = 'CRQ001261'
  ORDER BY Work_Log_ID desc

Why doesn't this work?

DECLARE @timestamp INT

SELECT @timestamp =  DATEDIFF(s,'1/1/1970 00:00:00',GETDATE())

SELECT DATEADD(s,@timestamp,'1/1/1970 00:00:00')

Substitute the @Timestamp with the value from your table.

You may need to multiply the int timestamp by 1000. The AR System stores the date as the number of 'seconds' where as most other languages store the unix timestamp as milliseconds (and as a long data type)

Hope that helps!

Go to

user tool -> Tools -> Options -> Locale tab -> Date/Time Style -> Custom Format -> mm/dd/yyyy