This worked for getting the difference between two...
# suitescriptc
Chris
03/14/2024, 6:23 PMThis worked for getting the difference between two dates last week:
ABS(TO_DATE({created},'MM/dd/yyyy HH:mi:ss')- TO_DATE({now},'MM/dd/yyyy HH:mi:ss')) * 60 * 24Chris
03/14/2024, 6:41 PMI dunno what changed, but this change works now:
ABS(TO_DATE(TO_CHAR({created},'MM/dd/yyyy HH:mi:ss'),'MM/dd/yyyy HH:mi:ss') - TO_DATE(TO_CHAR({now},'MM/dd/yyyy HH:mi:ss'),'MM/dd/yyyy HH:mi:ss')) * 60 * 24Chris
03/14/2024, 6:42 PMIt's just recasting the date into a format the
TO_DATEb
battk
03/14/2024, 6:50 PMTO_DATE works on character strings, and neither created nor now are character strings
battk
03/14/2024, 6:50 PMso it implicitly converts it to a character string, which will by default use the user's date and time settings
battk
03/14/2024, 6:52 PMwhich makes it vulnerable when those settings dont actually match what you said they were in the format you specified in TO_DATE
👍🏻 1
c
Chris
03/14/2024, 6:54 PMKey takeaway "which will by default use the user's date and time settings".
Chris
03/14/2024, 6:54 PMDon't implicitly trust things that move. 🙂
b
battk
03/14/2024, 6:57 PMmight actually be the database's format setting, but same idea
battk
03/14/2024, 6:58 PMim not actually sure why you are using TO_DATE or TO_CHAR in the first place
battk
03/14/2024, 6:58 PMyou can just subtract the two expressions
c
Chris
03/14/2024, 7:02 PMI tried that initially but I want the difference in minutes.
Chris
03/14/2024, 7:04 PMLast week {created} returned
MM/dd/yyyy HH:mi:ssMM/dd/yyyy HH:mi AM/PMTO_DATEb
battk
03/14/2024, 7:05 PMas in ({now} - {created}) * 60 * 24
battk
03/14/2024, 7:05 PMthe absolute isnt needed since now is always going to be after created
battk
03/14/2024, 7:06 PMand the TO_CHAR and TO_DATE are just converting a date into a char back into a date
c
Chris
03/14/2024, 7:07 PMI'll try ({now} - {created}) * 60 * 24 and see if it's the same
b
battk
03/14/2024, 8:41 PMtaking another look at this, you probably want to take a much closer look at your time format, HH is the 12 hour format, which means you are essentially losing 12 hours in the pm
watching following 2