dst_from_date

View All Public Scripts Login to Run Script

Script Name dst_from_date
Visibility Unlisted - anyone with the share link can access
Description Column with date datatype is failed to convert dst changes through new_time function
Area Oracle 12c
Contributor rags2107@gmail.com
Created Saturday February 23, 2019

Statement 1

select new_time(to_date('01-01-2018 00:00:00','DD-MM-YYYY'),'CST','DST') from dual

ORA-01830: date format picture ends before converting entire input string

More Details: https://docs.oracle.com/error-help/db/ora-01830

Statement 2

select new_time(to_date('01-01-2018 00:00:00','DD-MM-YYYY HH24:MI:SS'),'CST','DST') from dual

ORA-01857: not a valid time zone

More Details: https://docs.oracle.com/error-help/db/ora-01857

Statement 3

select new_time(to_date('01-01-2018 00:00:00','DD-MM-YYYY HH24:MI:SS'),'CST','EST') from dual


NEW_TIME(TO_DATE('01-01-201800:00:00','DD-MM-YYYYHH24:MI:SS'),'CST','EST')
01-JAN-18

NEW_TIME(TO_DATE('01-01-201800:00:00','DD-MM-YYYYHH24:MI:SS'),'CST','EST')
01-JAN-18

Statement 4

select new_time(to_date('01-01-2018 23:30:00','DD-MM-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-01-201823:30:00','DD-MM-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18

NEW_TIME(TO_DATE('01-01-201823:30:00','DD-MM-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18

Statement 5

select new_time(to_date('01-01-2018 00:00:00','DD-MM-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-01-201800:00:00','DD-MM-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18

NEW_TIME(TO_DATE('01-01-201800:00:00','DD-MM-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18

Statement 6

select  
select new_time(to_date('01-JAN-2018 23:30:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 23:30:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual

ORA-00936: missing expression

More Details: https://docs.oracle.com/error-help/db/ora-00936

Statement 7

select new_time(to_date('01-JAN-2018 23:30:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 23:30:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201823:30:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201823:30:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18 02-JUN-18

NEW_TIME(TO_DATE('01-JAN-201823:30:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201823:30:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18	02-JUN-18

Statement 8

select new_time(to_date('01-JAN-2018 23:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 23:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201823:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201823:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18 02-JUN-18

NEW_TIME(TO_DATE('01-JAN-201823:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201823:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18	02-JUN-18

Statement 9

select new_time(to_date('01-JAN-2018 22:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 22:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18 02-JUN-18

NEW_TIME(TO_DATE('01-JAN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-18	02-JUN-18

Statement 10

select new_time(to_date('01-JAN-2018 21:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 21:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201821:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201821:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18 01-JUN-18

NEW_TIME(TO_DATE('01-JAN-201821:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201821:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18	01-JUN-18

Statement 11

select new_time(to_date('01-JAN-2018 21:59:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 21:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201821:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18 01-JUN-18

NEW_TIME(TO_DATE('01-JAN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201821:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18	01-JUN-18

Statement 12

select new_time(to_date('01-JAN-2018 21:59:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 21:59:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18 01-JUN-18

NEW_TIME(TO_DATE('01-JAN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-18	01-JUN-18

Statement 13

alter session set nls_date_format ='DD-MON-YYYY HH24:MI:SS'

Statement processed.

Statement 14

select new_time(to_date('01-JAN-2018 21:59:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 21:59:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-2018 23:59:00 01-JUN-2018 23:59:00

NEW_TIME(TO_DATE('01-JAN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201821:59:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
01-JAN-2018 23:59:00	01-JUN-2018 23:59:00

Statement 15

alter session set nls_date_format ='DD-MON-YYYY HH24:MI:SS'

Statement processed.

Statement 16

select new_time(to_date('01-JAN-2018 22:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') , 
new_time(to_date('01-JUN-2018 22:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual


NEW_TIME(TO_DATE('01-JAN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT') NEW_TIME(TO_DATE('01-JUN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-2018 00:00:00 02-JUN-2018 00:00:00

NEW_TIME(TO_DATE('01-JAN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')	NEW_TIME(TO_DATE('01-JUN-201822:00:00','DD-MON-YYYYHH24:MI:SS'),'CST','EDT')
02-JAN-2018 00:00:00	02-JUN-2018 00:00:00

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database