Created
October 8, 2019 16:10
-
-
Save dmcghan/6da9a4c55e587ad4455bf41475c75956 to your computer and use it in GitHub Desktop.
hh:mi offset from sysdate
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table t ( | |
c date | |
); | |
insert into t (c) values (to_date('01.10.2019 06:45', 'mm-dd-yyyy hh24:mi')); | |
insert into t (c) values (to_date('01.10.2021 21:45', 'mm-dd-yyyy hh24:mi')); | |
select sysdate current_date, | |
to_date(to_char(sysdate, 'dd-mm-yyyy') || to_char(c, 'hh24') || to_char(c, 'mi'), 'dd-mm-yyyyhh24mi') new_date, | |
cast(to_date(to_char(sysdate, 'dd-mm-yyyy') || to_char(c, 'hh24') || to_char(c, 'mi'), 'dd-mm-yyyyhh24mi') as timestamp) - systimestamp diff, | |
extract(hour from cast(to_date(to_char(sysdate, 'dd-mm-yyyy') || to_char(c, 'hh24') || to_char(c, 'mi'), 'dd-mm-yyyyhh24mi') as timestamp) - systimestamp) hour_diff, | |
extract(minute from cast(to_date(to_char(sysdate, 'dd-mm-yyyy') || to_char(c, 'hh24') || to_char(c, 'mi'), 'dd-mm-yyyyhh24mi') as timestamp) - systimestamp) mi_diff, | |
extract(hour from cast(to_date(to_char(sysdate, 'dd-mm-yyyy') || to_char(c, 'hh24') || to_char(c, 'mi'), 'dd-mm-yyyyhh24mi') as timestamp) - systimestamp) || ':' || abs(extract(minute from cast(to_date(to_char(sysdate, 'dd-mm-yyyy') || to_char(c, 'hh24') || to_char(c, 'mi'), 'dd-mm-yyyyhh24mi') as timestamp) - systimestamp)) res | |
from t |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment