TRUNC is a multiple of 0.20833333333
I came from the world of SQL Server and asked to find out what was going on with part of the (ancient) Oracle script.
CAVEAT : I don't have access to the Oracle server, I just have a script that someone has emailed me and our copied data to the SQL Server.
I have no access to tasks that were moving data from Oracle to SQL Server. I'm pretty much in the dark - I have an Oracle script and (most) converted SQL Server tables.
I'm good at most of them using google, but I can't figure out in my entire life what it does:
decode(TRUNC((fin_time-start_time)/0.208333333333333),0,1,1,2)
I think I have interpreted the part decode
correctly equivalent:
case TRUNC((fin_time-start_time)/0.208333333333333)
when 0 then 1
when 1 then 2
end
But the TRUNC function is confusing me. endtime
and starttime
are times without a date (for example, 16:00:00
or 18:15:00
). Again, with no access or any experience with Oracle, I don't know if it means TIME datatype there, or if it's just DATETIME with default "datestamp" (for example 1900-01-01
). In any case, the date is either missing or irrelevant.
The documentation seems to suggest that TRUNC()
passed without a format parameter will simply split the time portion. But part of the time is whatever the column contains. So presumably the result 0
is divisible by 0.2083 ... I'm either totally wrong, or The Daily WTF is decent (or I suspect both).
Any ideas?
UPDATE . I think I have now correctly interpreted it as:
case floor(cast(dateadd(minute, DATEDIFF(minute, START_TIME, FIN_TIME), 0) as float) / 0.2083333333333)
when 0 then 1
when 1 then 2
end
It seems to return the expected values, but I think it's still WTF since values other than 0 and 1 are returned if I misinterpret the behavior DECODE()
. I am guessing that for unclosed cases without a default parameter, it will return NULL
(how case
)?
source to share
Subtracting two dates in Oracle returns a number that represents the number of days .
Example:
SELECT TO_DATE('03.12.2004 13:00:00','DD.MM.YYYY HH24:MI:SS')
- TO_DATE('03.12.2004 01:00:00','DD.MM.YYYY HH24:MI:SS')
from dual ;
Result: 0.5
This expression returns:
-
0
if the difference betweenfin_time
andstart_time
is less than 5 hours, -
1
if the difference is greater than or equal to 5 hours, but less than 10 hours, -
null
, otherwise.
source to share