CAST (변환하고자 하는 새로운 데이터타입으로 출력)

게시일: Jun 03, 2020 12:59:3 AM

- 변환하고자 하는 새로운 데이터타입으로 출력

- 출력값이 지정한 타입보다 크면 에러 발생

select cast(to_date('2020-05-13 12:34:56', 'yyyy-mm-dd hh24:mi:ss') as varchar(18)) -- ORA-01801: date format is too long for internal buffer

from dual

;

select cast(to_date('2020-05-13 12:34:56', 'yyyy-mm-dd hh24:mi:ss') as varchar(19)) -- '2020-05-13 12:34:56'

, cast(to_date('2020-05-13 12:34:56', 'yyyy-mm-dd hh24:mi:ss') as varchar(50)) -- '2020-05-13 12:34:56'

, cast(to_date('2020-05-13 12:34:56', 'yyyy-mm-dd hh24:mi:ss') as varchar2(19)) -- '2020-05-13 12:34:56'

, cast(to_date('2020-05-13 12:34:56', 'yyyy-mm-dd hh24:mi:ss') as varchar2(50)) -- '2020-05-13 12:34:56'

, cast(123 as varchar(3)) -- '123'

, cast(123 as varchar(4)) -- '123'

, cast(123.45 as number(3)) -- '123'

, cast(123.45 as number(4)) -- '123'

, cast(123.45 as number(6,2)) -- '123.45'

, cast(123.45 as number(9,3)) -- '123.45'

, cast('123.45' as number(9,3)) -- '123.45'

-- subquery 에 cast 사용

, cast((select '123.45'

from dual) as number(6,2)) -- '123.45'

from dual

;

select cast('13-MAY-2020' as timestamp) -- 2020-05-13 00:00:00.000000

, cast('13-MAY-2020' as timestamp with local time zone) -- 2020-05-13 00:00:00.000000

, cast('2020-05-13' as date) -- 2020-05-13 00:00:00

, cast('2020-05-13 12' as date) -- 2020-05-13 12:00:00

, cast('2020-05-13 12:34' as date) -- 2020-05-13 12:34:00

, cast('2020-05-13 12:34:56' as date) -- 2020-05-13 12:34:56

from dual

;

select timestamp'2020-05-13 12:34:56' -- 2020-05-13 12:34:56.000000000

, to_timestamp('2020-05-13 12:34:56', 'yyyy-mm-dd hh24:mi:ss') -- 2020-05-13 12:34:56.000000000

from dual