as alternate i have tried to convert in the oracle SQL and used the below function
TO_CHAR (TO_DATE(a.APP_DATE ,'yyyymmdd'),'ddmmyyyy')as approved_date
when i tried to load through custom sql, its throwing below error
The problem relates to the the format of the field vs what you are trying to do with it... Is app_date already a date? if so, you don't need TO_DATE()
For example this works > select TO_CHAR(sysdate,'dd-MON-yyyy') from dual
There is some useful stuff in this thread > ORA-01841 :(full) year must be between -4713 an... | Oracle Community
And here > Oracle/PLSQL Date Functions
Thanks Mark, the field from oracel table is CHAR and i converted to using to_date function.I tried using to char functions as well. Any suggestions pls ?
Below is the error from tableau end
Oracle database error 1841: ORA-01841: (full) year must be between -4713 and +9999, and not be 0.
if app_date is a CHAR, do you need to swap it to a date? or leave it as CHAR?
I ask because at the moment, app_Date CHAR, you swap to DATE, then back to CHAR?!
TO_CHAR (TO_DATE(a.APP_DATE ,'yyyymmdd'),'ddmmyyyy') as approved_date
I substituted app_date for a string (today's date), all the below work
select TO_CHAR (TO_DATE('04/02/2016' ,'DD-MM-YYYY'),'DD-MM-YYYY') as approved_date from dual; -- leaves as CHAR, after swapping to date
select TO_DATE('04/02/2016' ,'DD-MM-YYYY') as approved_date from dual; -- swaps CHAR to DATE
select TO_DATE(TO_CHAR (TO_DATE('04/02/2016' ,'DD-MM-YYYY'),'DD-MM-YYYY'),'DD-MM-YYYY') as approved_date from dual; -- swaps CHAR to DATE THEN CHAR then DATE!
The main thing is the format of the 'date', if you swap my working one examples from DD-MM-YYYY to DDMMYYYY they fail (they don't match the input string).
IF appdate was formatted as 04022016 then DDMMYYYY would work > select TO_DATE('04022016' ,'DDMMYYYY') as approved_date from dual;
Thanks Mark, i need to change to date from char as i would be placing date filter in the dashboard(start and end date as parameter).I will give a try and let you know.
As alternate, i used mid function to manipulate the string to change in ddmmyy from yyyyddmm and then apply date() function, to return date from string.the filter seems to be working.
Anyways thanks for your help.I will check your solutions as well
Great you got something working!
I think you are close, its just about keeping any eye on the format of the input (the appt date) vs how you reference (the appt date format) in the SQL, they need to match.
If you need any further help, let me know