I'm having trouble with the following in saved sea...
# suiteanalyticsg
Gregory Jones
02/25/2019, 2:31 PMI'm having trouble with the following in saved search criteria: CASE WHEN {usernotes.notedate} >= {today}-7 THEN 1 ELSE 0 END. It works, but it's taking time into account, and it needs to be based on the day only. What do i need to do to it?
a
Ankur Sanghi
02/25/2019, 2:34 PMuse trunc to truncate the field to only date then compare
g
Gregory Jones
02/25/2019, 2:38 PM@Ankur Sanghi Thanks! Do I have to specify a format like 'mm/dd/yyyy'?
a
Ankur Sanghi
02/25/2019, 2:41 PMyes, quick ref link: https://www.techonthenet.com/oracle/functions/trunc_date.php
Ankur Sanghi
02/25/2019, 2:43 PMIn your example it would be trunc({datefield}, 'DDD') - i think. please validate that it works... or experiment some more
g
Gregory Jones
02/25/2019, 2:48 PMThank you! I'll see what I come up with.
Gregory Jones
02/25/2019, 2:51 PMCASE WHEN TRUNC({usernotes.notedate}) >= TRUNC({today})-7 THEN 1 ELSE 0 END - no format needed and it seems to be working! Thanks again!