10-135 時間を示す数値の相互変換

SQLパズル

勤務表などでよくある形式の、
以下の数値の相互変換を行う

700  ⇔  7.0
706  ⇔  7.1
712  ⇔  7.2
718  ⇔  7.3
724  ⇔  7.4
730  ⇔  7.5
736  ⇔  7.6
742  ⇔  7.7
748  ⇔  7.8
754  ⇔  7.9
800  ⇔  8.0

SQL

--■■■700 ⇒ 7.0 の変換■■■
select Val as "変換前",trunc(val/100) +mod(val,100)/60 as "変換後"
from (select 700 as Val from dual
union select 706 from dual
union select 712 from dual
union select 718 from dual
union select 724 from dual
union select 730 from dual
union select 736 from dual
union select 742 from dual
union select 748 from dual
union select 754 from dual
union select 800 from dual);

--■■■7.0 ⇒ 700 の変換■■■
select Val as "変換前",trunc(val) * 100 + 60*mod(Val,1) as "変換後"
from (select 7.0 as Val from dual
union select 7.1 from dual
union select 7.2 from dual
union select 7.3 from dual
union select 7.4 from dual
union select 7.5 from dual
union select 7.6 from dual
union select 7.7 from dual
union select 7.8 from dual
union select 7.9 from dual
union select 8.0 from dual);

解説

mod関数で、剰余を求めてます

---