Hello all,

I am having trouble converting the date format on one of my report fields.

I have added the following line in my .fex:

-SET &YM=EDIT(&DATE,'9999-99-99 99:99:99');

The report data that I am working with comes in the following format (here is an example):

2015/02/17 15:42:37.087


When I run the report to test, I get the following error:

0 ERROR AT OR NEAR LINE 5 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: 05/0-5/-15
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


Any help would be greatly appreciated! Please let me know if you need any clarification on what I am trying to do.

Thanks,
Lewis

This message has been edited. Last edited by: lewiswhite1994,

Hi Lewis,

&DATE

is a reserved word in Focus

Try this and you will see the reason of your result:

-TYPE &DATE
-SET &YM=EDIT(&DATE,'9999-99-99 99:99:99');
-TYPE &YM

Use another variable name and it must work.

Lewis, Check out the Functions manual, the EDIT function uses '9' for accept and '$' for reject.

You will need to add '$' to remove the slashes, colons and periods.

Also can you post the focus code, your error says FIELDNAME IS NOT RECOGNISED, which looks like it is in a TABLE request.

Thank you for your responses! The next part of my question was to be able to view the fields but one hour in the past.

I have managed to get this to work with the following code:

TABLE FILE ECM_KPI_IVU_TRANS_T
PRINT STATS_TIME
COMPUTE
MGHT/HYYMDS = HADD(STATS_TIME, 'HOUR', -1, 8, 'HYYMDS');
WHERE RECORDLIMIT EQ 2
END

When I run the .fex, I do get a column with the time correctly one hour in the past, however, the format still shows seconds.
How can I get rid of the seconds? Here is the output (First column is above, and second column is shown below it):

STATS_TIME
2015/02/17 15:42:37:087
2015/02/17 15:42:37:087

MGHT
2015/02/17 14:42:37
2015/02/17 14:42:37

Thanks in advance

Try

MGHT/HYYMDI

You should check out the manuals on describing data.

e.g. Display Options

MGHT/HYYMDI worked perfectly, thank you MartinY!