Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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?
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;
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,