Hi @Chamaka Wimalarathne 

Try using to_date(date_field,'DD/MM/YYYY') or trunc(date_field)

Cheers

You can use to_char(date_field, 'MM/DD/YYYY') to display the date field as a string with your needed format.

Hi @EntNadeeL  tried both earlier , didnt work

@Tomas Ruderfelt  u need the output to be a date , to_char will make it a string right?

Hi @Chamaka Wimalarathne 

Below should work. I have tested it with a quick report.

trunc(TO_DATE('01 Jan 2018 08:00:00', 'DD-MON-YYYY HH24:MI:SS'))

Give it the right date format and then trunc it.

cheers

Hi @EntNadeeL 

It didnt work either . TO char works but i need something in date format

the version i use is 23R1

        a.c_sign_date "Sign Date of contract",
trunc(a.c_sign_date) "Sign Date of contract trunc",
to_date(a.c_sign_date,'DD/MM/YYYY')  "Sign Date of contract to date",
to_char(a.c_sign_date,'DD/MM/YYYY')  "Sign Date of contract to char",
trunc(TO_DATE(a.c_sign_date, 'DD-MON-YYYY HH24:MI:SS')) "Sign Date of contract to trunct todate",

@Chamaka Wimalarathne 

You are right. I was just checking from EE side. 

However it looks like this does not work for any version of Aurena. 

Let me check this a bit more and see if I can find anything.

 Hi @Chamaka Wimalarathne 

It looks like Aurena or Cloud adds the time portion by default if you use the date format in a quick report. You can check this with a standard field that does not have a time portion.

If you check the planned due date in customer order lines, it does not have a time portion. But if you take it into a quick report, it will show the time portion as  00.00. 

I believe this is something we will have to ask from IFS as it does not mention anywhere in the documentation.

Try this:

TO_CHAR(TO_DATE(SUBSTR(arrival_date, 1, 10), 'DD.MM.YYYY'), 'DD.MM.YYYY') AS converted_arrival_date

You can replace bolded fields.

Edit. Just noticed you have checked this with TO_CHAR, but it does not fit for you! :)
I do date range selection and this TO_CHAR will fit my purposes, but this may be something that does not help you.