Hola @Guillermo Cuadrado .
¡Gracias!
That´s our plan B, some splits functions etc etc. Also consider that for september, that month, is 4 letters in spanish from Oracle (other are 3) so we need to have first to "read" the first letter, see if is a "S" and then apply the split to extract the month etc etc
I think the issue here it´s that Apptio doesn´t understand the oracle mask (i tried several mask/formats etc etc) in the datalink and it´s not applied when the DL run and created the table (if i used thee same select in a SQL Developer i get the correct transformation)
Original Message:
Sent: 03-24-2023 05:51
From: Guillermo Cuadrado
Subject: To_Date Oracle Function in a Datalink
Hola, @Mario Luque
In CT, I tried the following:
Loaded your data from Excel (cannot do the ORACLE easily):
- I changed the Spanish abbreviations to English, as that is our Locale, but it should work "as is" for you".
- I created a new column to get rid of the time stamp, by using the Split() function. In the same formula, I eliminated the dot after the abbreviated month name with a Substitute() call:
- Now Apptio understands the column as a date formatted one, and I could do a Date Partition on the new column, just to be sure:
As you can see, CT recognizes it should only display data for JAN2023, so it is a proper date.
- I could have edited the original date column with the same procedure, but left it just for clarity.
I hope this helps.
------------------------------
Regards, Guillermo
Original Message:
Sent: 03-23-2023 03:52
From: Mario Luque
Subject: To_Date Oracle Function in a Datalink
Hello Team
We have one Datalink that is a Oracle select. We have this sentence (that works in a SQL Developer and give to us the correct mask/format)
SELECT
TO_DATE(to_char(FEC_INI_REC_COTR, 'dd/mm/yyyy'),'DD/MM/YYYY'),
TO_DATE(to_char(FEC_FIN_REC_COTR, 'dd/mm/yyyy'),'DD/MM/YYYY')
FROM DSS_LD.S29_HEC_REC_EXT
but in Apptio, in the table created ,we have this in that column:
01-abr.-2021 00:00:00
01-ene.-2023 00:00:00
08-oct.-2022 00:00:00
25-nov.-2020 00:00:00
Any way to remove the "hours"??. Any mask in the to_char or to_date Oracle function that the datalink understand ? We tried several types and always the same result
Thanks for your help
#BillTheTBMGuy
#TBMStudio