date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY') not working while converting YYMMDD date format

[P_Kale]

Hi,

In data i have date in below format 

TRANSACTION_DATE	CHDRNUM
231103	00022836
231110	00022836

which I am converting through date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY')  function. This function was working till yesterday but somehow i don't know today it is not working band date is getting converted into numeric value. Can you pl help to resolve issue.

Thanks in advance

[Or]

That expression works fine with your sample data.

I'd suggest looking into whether the data has changed - perhaps a non-conforming number has been inserted (e.g. 231301) that breaks the formatting?

[P_Kale]

Hi,

Thanks for help.

In where clause also I have use this function like given below. But in where clause it is working fine as I have checked the extracted data into excel & converted number into date format. The data is correct as per script. But in table chart when i am taking "Transaction_Date" it is showing number and while exporting in excel too.

Means back-end data is proper, something is happening at front-end.

PTRN_2:
Load
CHDRNUM,
DayName(PTRNEFF) as PTRNEFF_NEW,
//TRANSACTION_DATE,
date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY') as TRANSACTION_DATE,
TRANSACTION_TIME,
BATCTRCDE,
VALIDFLAG

Resident PTRN_1 Where VALIDFLAG <> 2 and  date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY') >= '01/11/2023';
Drop Table PTRN_1;

[P_Kale · Accepted solution]

Hi,

Looks little bit strange but I able to achieve through below given way..

DATE(date(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY') AS TRAN_DATE,