topic Re: date(date#(TRANSACTION_DATE,'YYMMDD'),'DD/MM/YYYY') not working while converting YYMMDD date format in App Development

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

P_Kale — Thu, 28 Dec 2023 07:47:32 GMT

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

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

Or — Thu, 28 Dec 2023 08:35:14 GMT

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?

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

P_Kale — Thu, 28 Dec 2023 09:08:54 GMT

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;

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

P_Kale — Thu, 28 Dec 2023 10:50:54 GMT

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,