which search function will convert 8:30 to a numbe...
# suitescripta
Ali
05/04/2021, 3:40 PMwhich search function will convert 8:30 to a number?
Ali
05/04/2021, 4:48 PMI've tested TO_NUMBER and TO_NUMBER(TO_CHART(()) and it didn't work
b
battk
05/04/2021, 5:58 PMwhat number would you want it to be
a
Ali
05/04/2021, 5:58 PM@battk float number
Ali
05/04/2021, 5:59 PM8.5
b
battk
05/04/2021, 5:59 PMyou would need to split it into 2 strings
battk
05/04/2021, 5:59 PMand then add them
a
Ali
05/04/2021, 6:00 PMhow can I do that inside search
Ali
05/04/2021, 6:00 PMI want to compare it with another number
Ali
05/04/2021, 6:00 PMand if it's bigger show the result
Ali
05/04/2021, 6:01 PMI want to compare it with another number as a search criteria
b
battk
05/04/2021, 6:04 PMTO_CHAR would be my choice
a
Ali
05/04/2021, 6:06 PMI've used TO_NUMBER(TO_CHAR(field))
Ali
05/04/2021, 6:06 PM<other field
Ali
05/04/2021, 6:06 PMbut didn't work
Ali
05/04/2021, 6:08 PMTO_CHAR will convert it to text but I need to compare numbers
b
battk
05/04/2021, 6:09 PMuse TO_CHAR with the appropriate Datetime Format Models
battk
05/04/2021, 6:09 PMthen you can convert it to a number
a
Ali
05/04/2021, 6:19 PM@battk I don't think there is something on the above link that can help with converting 8:30 to 8.5
Ali
05/04/2021, 6:19 PMat least I can't see
b
battk
05/04/2021, 6:20 PMconvert it to 8 and 30
battk
05/04/2021, 6:20 PMdivide 30 by 60 and add it to 8
a
Ali
05/04/2021, 6:21 PMis it possible to do this in the search formula?
Ali
05/04/2021, 6:22 PMaccording to this link
Ali
05/04/2021, 6:22 PM Copy code
TO_NUMBER(TO_CHAR({hours}))Ali
05/04/2021, 6:23 PMI can use your solution outside search but I'm not sure how to do it in the search formula
b
battk
05/04/2021, 6:25 PMstep one, make your formula get the minutes
battk
05/04/2021, 6:25 PMyou have to use the fmt parameter of TO_CHAR
a
Ali
05/04/2021, 6:27 PMis there somewhere that I can test these format models
b
battk
05/04/2021, 6:32 PMyou can try searching online for things to run sql online
battk
05/04/2021, 6:32 PMi dont think it will be any better than simply making a text formula with whatever formula you want
a
Ali
05/04/2021, 6:37 PM@battk I've tested some of them but the issue is there is no fmt for 8:30 in that link
Ali
05/04/2021, 6:38 PMhow can I simply make a text formula
b
battk
05/04/2021, 6:39 PMyou will be using TO_CHAR twice
battk
05/04/2021, 6:39 PMonce for the minutes, once for the hours
battk
05/04/2021, 6:39 PMdont try combining them
a
Ali
05/04/2021, 6:41 PMyes that's correct but I can't get the result for them
Ali
05/04/2021, 6:44 PMI can't find the correct fmt parameter
b
battk
05/04/2021, 6:44 PMwhat have you tried
a
Ali
05/04/2021, 6:44 PM:
Ali
05/04/2021, 6:44 PM99.99
Ali
05/04/2021, 6:44 PM99:99
Ali
05/04/2021, 6:44 PM:MI
Ali
05/04/2021, 6:47 PMMI
b
battk
05/04/2021, 6:47 PMthat last one should work
a
Ali
05/04/2021, 6:47 PMno it didn't
b
battk
05/04/2021, 6:48 PMwhats your full formula
a
Ali
05/04/2021, 6:48 PMTO_CHAR({hours}, 'MI')
Ali
05/04/2021, 6:49 PMERROR: Invalid Expression
b
battk
05/04/2021, 6:49 PMand which record is being searched
a
Ali
05/04/2021, 6:50 PMtimebill
b
battk
05/04/2021, 6:54 PMthat might not actually be time of day
a
Ali
05/04/2021, 6:54 PMI think that's not
b
battk
05/04/2021, 6:55 PMtreat it as a string then
a
Ali
05/04/2021, 6:55 PMok
b
battk
05/04/2021, 6:55 PMthe hours is everything upto the
':'battk
05/04/2021, 6:56 PMminutes everyhing after the
':'a
Ali
05/04/2021, 6:56 PMwhat function can help
Ali
05/04/2021, 6:56 PMinside the formula
a
Ali
05/04/2021, 6:56 PMis there a list of functions with details
b
battk
05/04/2021, 7:04 PMyou can cut it down to REGEXP_SUBSTR if you want to use a regular expression
a
Ali
05/04/2021, 7:24 PMThanks
2 Views