this formula is working on and off. most of the ti...
# generalj
Joseph
03/21/2023, 5:05 PMthis formula is working on and off. most of the time it works, but every once in a while it gives me this error. i'm assuming the issue is somehow it's getting the wrong data type, how do i prevent that? i feel like i'm losing my mind
Copy code
round(to_date({today},'MM/DD/YYYY')-to_date({custbody_wo_release_date},'MM/DD/YYYY'),0)m
Marc
03/21/2023, 6:05 PMYour field {custobody_wo_release_date} it’s always like MM/DD/YYYY ?
j
Joseph
03/21/2023, 6:22 PMyes its a date field. do i need to NVL() in the case that filed is empty? this is such a simple operation i can't believe it's giving me issues
m
Marc
03/21/2023, 6:27 PMYes try with NVL
Marc
03/21/2023, 6:27 PMROUND(TO_DATE({today},'MM/DD/YYYY') - NVL(TO_DATE({custbody_wo_release_date},'MM/DD/YYYY'),TO_DATE('01/01/2023','MM/DD/YYYY')), 0)
It’s an example with 01/01/2023
e
Edgar Valdés
03/21/2023, 6:51 PMMaybe it's always a date field, but not always in 'MM/DD/YYYY' format. It can depend on the user preferences.
a
AI1
03/21/2023, 11:34 PM@Joseph is this in a saved search?
j
Joseph
03/21/2023, 11:35 PMYes
a
AI1
03/21/2023, 11:37 PMWhy do you add to_date? Whenever I’ve subtracted dates just doing it with the fields has always worked
Ie. ROUND( {today} - {custbody_wo_releasedate}, 1)
3 Views