How to Display Date Time field within a text field in Date Time Format (M-d-yy h:mm tt) in local time and not GMT
How to Display Date Time field within a text field in Date Time Format (M-d-yy h:mm tt) in local time and not GMT.
The challenge is, when I use DATEFORMAT function within the text field calculation, the time is set as GMT. I am aware that this is how DATEFORMAT function works. But looking to see if anyone tried/has found a way around to convert this back to local time. The reason for using text field is I am concatenating values from two different fields (one is text and another is Date field in Date Time format)
Archer stores all times as GMT in the database, but converts them all to the user's local time based on their user profile time zone setting for display in the UI. You shouldn't have to do anything to get it to display in local time other than make sure your user base is correctly setup with their timezones.
I think it might be true if the field type is date time. But in my text field when I concatenate some text along with date formatted date time field, I get the GMT displayed which is 5 hours offset. Also if I don't use DATEFORMAT function the date is displayed within text field as a numeric value
Right - All of this would be expected behavior.
May I ask why you are calculating into a text field instead of date/time field? If you have to use text, then you will have to handle the date formatting and time zone conversions on your own.
I also have a similar query in Archer.
I have a csv data feed that has the date in EPOCH Unix time format like 1470873804582. I have written a formula to convert the date to date,time in the filed of DATE type.
([TimeStamp]/(60*60*24*1000)+ DATEFORMAT("1/1/1970","yyyy/mm/dd hh:mm:ss")) where TimeStamp is my column name in the data feed. I am getting an unexpected error. " Value is not a valid number" error in the logs.
Abhishek you might want to create two calculated fields in the data feed. One for the data time conversion and the other to format that calculation and see if that works.
The data which I get in directly from the RiskMinder tool. I get a csv data feed that cannot be altered as I will schedule my DFM.
In this case, please correct me if I am wrong : I can not create 2 separate fields and have to go with a single column as TimeStamp mentioned in csv data feed.
Abhishek in the data feed on the Source Definition tab click the Add New link. This will add new field at the bottom to the Source Field listing.
There you can give a name and change the Field Type to Calculation. When you are done with that calculation for TimeStampConversion repeat the steps for TimeStampFormat. Then on the Data Map tab you can map the TimeStampFormat field to the appropriate field.
I have made changes as you mentioned and have the screen shots below. New field created is TimeConversion but I do not see any appropriate field to map. It means, I have to create a separate filed to map TimeConversion ?
Yes, you will need to create the field(s) in the application to map to separately from the fields you added to the data feed's source definition.