Solved

Date format in IFS Active Work Order screen

  • 18 August 2021
  • 5 replies
  • 297 views
N
Rank
Userlevel 2
Badge +7

Hi,

 

I want to change the date format for a particular column in the Active Work Order screen which is showing it as yyymmdd and I want to change it to be dd/mm/yyyy. Can you tell me where I need to make this change please? It is show in the highlighted line in the image below.

Thanks.

Neil.

icon

Best answer by Neil Guild 24 August 2021, 17:23

View original

5 replies

N
Rank
Userlevel 2
Badge +7

Hi Guys,

Is there anybody out there who can help with this little problem, please?

I would appreciate any help :-).

Cheers,

Neil.

A
Rank
Userlevel 7

Are those custom fields that you have added or what fields are those? 

N
Rank
Userlevel 2
Badge +7

Hi @anmise ,

 

Thanks for your reply :-).

 

Further investigation has revealed that they are indeed custom fields. The SQL statement is returning a string value directly from another table which is in that format (i.e. yyyymmdd hh:mi:ss)

SELECT COALESCE(MIN(old_Value), MIN(New_Value))
FROM history_log_tab a
JOIN history_log_attribute_tab b
     ON a.log_id = b.log_id 
WHERE REPLACE(REPLACE(a.Keys, '^',''), 'WO_NO=','') = :Wo_No
AND a.history_type = 2 
AND b.column_name = 'PLAN_F_DATE'
 

I suppose that I need use a to_date function to convert this string to the correct date format (i.e. ‘dd/mm/yyyy hh:mi:ss’) but I’m not sure where to try to put it.

I have tried to convert the old_Value/New_Value directly in the SELECT statement.

TO_DATE(COALESCE(MIN(old_Value), MIN(New_Value)),'dd/mm/yyyy hh:mi:ss')

FROM history_log_tab a
JOIN history_log_attribute_tab b
     ON a.log_id = b.log_id 
WHERE REPLACE(REPLACE(a.Keys, '^',''), 'WO_NO=','') = :Wo_No
AND a.history_type = 2 
AND b.column_name = 'PLAN_F_DATE'
 

but the select statement text is blue (instead of black).

Can you help any further, please?

 

Cheers,

Neil.

 

N
Rank
Userlevel 2
Badge +7

Hi @anmise ,

 

I have seen that the text turning blue is simply an indication that the SQL statement has been modified. I tried clicking on the Published checkbox and the text turned black. So I am happy with that, however the SQL statement (with the to_date functionality in my previous post) which works in PL/SQL developer causes the message ‘Not all bind variables have a corresonding argument’ to be displayed when clicking on Finish in the Edit Custom Objects dialogue. Very frustrating.

 

Do you know why IFS is complaining about bind variables in the statement which includes the to_date functionality?

 

Cheers,

Neil.

 

N
Rank
Userlevel 2
Badge +7

Hi @anmise and anybody else out there who might be interested ;-).

 

It seems that IFS SQL Statement cannot ignore the : character and always interprets it as a bind variable indicator. So, I had to manipulate the SQL statement to reformat the date that was held on the database as yyyymmdd hh:mi:ss. Here is the SELECT statement showing this manipulation:

SELECT 
SUBSTR(COALESCE(MIN(SUBSTR(old_Value,1,8)), MIN(SUBSTR(New_Value,1,8))),7,2) ||
'/' ||
SUBSTR(COALESCE(MIN(SUBSTR(old_Value,1,8)), MIN(SUBSTR(New_Value,1,8))),5,2) ||
'/' ||
SUBSTR(COALESCE(MIN(SUBSTR(old_Value,1,8)), MIN(SUBSTR(New_Value,1,8))),1,4) ||
' ' ||
SUBSTR(COALESCE(MIN(old_Value), MIN(New_Value)),10,8)
 

FROM TABLE_NAME.

old_Value and New_Value are the database columns with the date in yyyymmdd hh:mi:ss format.

 

And so this is solved, thanks. :-).

 

Cheers,

Neil.

 

Reply


Terms & ConditionsCookie settings