like this

select
  to_char(sysdate,'WW-YYYY'),
  to_char(sysdate,'IW-YYYY')
from dual

Depends on day, those could return different week number ;)

09-2020    09-2020

Hi Jouni

I must start by thanking you for your response and help. I must now tell you that i am new to SQL but learning quick.

I only need to get e week number from one date and this need to be a new created column, may be with the AS command

I have a couple of question if i may;

First question

In my scenario does to_char = ACTUAL_FINISH?

Second question

are you giving me two different solutions or are they one command?

Giving my sequence above how would you insert it?

I have tried every way i can think of and it wont work.

Thank you in advance 

Like this,

select JT_TASK_UIV.STATE, JT_TASK_UIV.TASK_SEQ, JT_TASK_UIV.ACTUAL_OBJECT_DESCRIPTION, JT_TASK_UIV.REPORTED_DATE, JT_TASK_UIV.ACTUAL_FINISH, JT_TASK_UIV.ERROR_TYPE, JT_TASK_UIV.PERFORMED_WORK, JT_TASK_UIV.SITE, JT_TASK_UIV.ITEM_CLASS_ID, JT_TASK_UIV.ACTUAL_START, JT_TASK_UIV.ACTUAL_OBJECT_ID as "Serial Object", JT_TASK_UIV.PRIORITY_ID, JT_TASK_UIV.DESCRIPTION, EQUIPMENT_SERIAL_UIV.MCH_CODE, EQUIPMENT_SERIAL_UIV.MCH_LOC, EQUIPMENT_SERIAL_UIV.MCH_POS, to_char( JT_TASK_UIV.ACTUAL_FINISH,’WW’) AS week

from &AO.JT_TASK_UIV LEFT OUTER JOIN &AO.EQUIPMENT_SERIAL_UIV ON  &AO.JT_TASK_UIV.ACTUAL_OBJECT_ID = &AO.EQUIPMENT_SERIAL_UIV.MCH_CODE 

WHERE STATE IN ('Work Done','Finished','Released','Started','FaultReport','Work Started')


Problem could be if JT_TASK_UIV.ACTUAL_FINISH is not date
 

Hi Jouni,

Thanks again,

That did not work, i have screen shot the error message

Its a date and time and it is displayed like this,

30/01/2020 10:09:25

Dave

Hi Jouni,

 

Thanks again,

That did not work, i have screen shot the error message

Its a date and time and it is displayed like this,

30/01/2020 10:09:25

 

Dave

Hard to see that tiny screenshot but this works for me.

SELECT jt_task_uiv.state,
 jt_task_uiv.task_seq,
 jt_task_uiv.actual_object_description,
 jt_task_uiv.reported_date,
 jt_task_uiv.actual_finish,
 jt_task_uiv.error_type,
 jt_task_uiv.performed_work,
 jt_task_uiv.site,
 jt_task_uiv.item_class_id,
 jt_task_uiv.actual_start,
 jt_task_uiv.actual_object_id AS "Serial Object",
 jt_task_uiv.priority_id,
 jt_task_uiv.description,
 equipment_serial_uiv.mch_code,
 equipment_serial_uiv.mch_loc,
 equipment_serial_uiv.mch_pos,
 TO_CHAR(jt_task_uiv.actual_finish, 'IW') AS week
FROM &ao.jt_task_uiv
LEFT OUTER JOIN &ao.equipment_serial_uiv ON &ao.jt_task_uiv.actual_object_id = &ao.equipment_serial_uiv.mch_code
WHERE state IN ('Work Done', 'Finished', 'Released', 'Started', 'FaultReport', 'Work Started')

If you are using &AO. in SQL Query Tool, make sure to enter ‘IFSAPP.’  (without single quotes) in the variable definition box.

Hi Jouni,

Perfect, that works.

Thank you so much for your help.

Dave