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

How to convert remedy dates, Any idea on how to convert them now to date formats?! + (( (col + 3)/(606024)), 'DD/MM/YYYY HH:MI:SS') as new_field say that this field is attached to the number column which store a date in timestamp format. (Japanese environment only) On the Web tab of the AR System User Preferences form, do not use the following format in the Display Date/Time Style (Web) field with the Custom setting in a Japanese environment: EEEE, MMMM dd, yyyy Otherwise, the BMC Remedy Mid Tier returns ARERR 9376 when a browser user tries to modify an entry in any form.

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.

Remedy - Server, There is no BMC function to change the format for a date field for workflow. In this scenario, the OS system is configured for dd/mm/yyyy� 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).

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!

Remedy - AR System - Server, How to convert the EPOCH Time Integer format of ARS date/time fields SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY� ArLiteDTMConv Utility help converting BMC Remedy Date-Time values, Form ARS External-To-Internal to Internal-To-External format and Vise-Versa. You can set the result to a variable and then use the variable in your script or when passing as parameter or introduce in any DB criteria for both Window and Linux .

Go to

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

How to convert between Oracle Date/Time Field a, How can the Oracle datetime field be converted into Remedy TIMESTAMP seconds? How can the Remedy Date/Time seconds field be converted into a readable character Step1: Query the date field in question and display the format. select to_char(testdate, 'MM-DD-YYYY HH24:MI:SS) from testdata By default, date is displayed in mmm d, yyyy format, for example, Oct 22, 2016, and time is displayed in 12-hour format. AM and PM specifications are displayed in h:mm a. format, for example: 3:15 am. This format is the default, and it does not depend on region, device settings, or the supported language in Smart IT. Note that you cannot modify

How to change Date format in Web/Report?, Share This: This document contains official content from the BMC Software Knowledge Base. Custom Date Format -> MM/dd/yyyy. Custom� Format. Based on the type of field that the column being formatted is there are various format options. The ones listed below come default with BMC Remedy Smart Reporting, however as this is customizable there may be additional ones that comes as part of your installation.

Custom SQL, that timestamp or convert it to a specific datetime format (like mm/dd/yyyy). Have you got the pre-defined ITSM universe that BMC Remedy� Converting a BMC Remedy timestamp to mm/dd/yyyy format 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

Attributes Tab - Automic, RA BMC Atrium; RA BMC Atrium - Agent Guide; RA BMC Remedy; RA BMC The exact format of the selected data type (exception: "String") can be determined using the attribute "Output Format". You can easily change "Number", " Timestamp", "Time", and "Date" to "Text". Output Format: YYYY-MM-DD HH:MM :SS DATEFORMAT / timestamp_format behavior discrepancy with documentation. ODBC support for TIMESTAMP WITH TIME ZONE. How do you convert a DateTime to Gregorian? Automatic type conversion to string. Configuration file under Linux. how to start Sysbase in LINUX? How to automatically source sa_config.sh on httpd restart?

Comments
  • 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.
  • 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')))