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

Question

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!

score 3 · Answer 1 · edited Jul 26 '12 at 08:22

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

score 0 · Answer 2 · edited Oct 22 '12 at 00:47

0

Go to

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

edited Oct 22 '12 at 00:47

Kjuly

34,476
22
104
118

answered May 02 '12 at 19:00

Sid

1

score 0 · Answer 3 · answered Oct 04 '11 at 17:51

0

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.

answered Oct 04 '11 at 17:51

Wil

4,130
1
16
15

That's the problem - I can't use anything but DATEADD on this timestamp. For whatever reason, I can't use DATEDIFF or any other DATE function - I get an "invalid type" error. – CXL Oct 04 '11 at 18:22
So when you do `DATEADD(s,timestamp,'1/1/1970 00:00:00')` it doesn't throw an error? Does this work? `SELECT DATEADD(dd,0,DATEDIFF(dd,0,DATEADD(s,timestamp,'1/1/1970 00:00:00')))` – Wil Oct 04 '11 at 18:28

score 0 · Answer 4 · answered Oct 05 '11 at 23:34

0

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!

answered Oct 05 '11 at 23:34

jdr120

23
1
5

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

4 Answers4