I have a column with a datatype number but I want to convert the column into date. I tried using CAST function but it gives error
ORA-00932: inconsistent datatypes: expected DATE got NUMBER.
For example, 20221203 to 2022-12-03.
Any suggestions?
col_date is the column name
select cast(col_date as date)
from school
CodePudding user response：
Use the to_date() function:
select to_date(col_date, 'YYYYMMDD')
from school
That does an implicit conversion from number to string, but you can make it explicit:
select to_date(to_char(col_date), 'YYYYMMDD')
from school
Of course, it would be better to store your values as proper dates. You may have numbers which don't correspond to actual dates, and will need to decide how to handle those if you do.
Oracle's date datatype always has a time component, which will be set to midnight with this conversion. They have no intrinsic human-readable format - your client decides how to display, usually using your session NLS_DATE_FORMAT setting. You can change that with alter session, which will affect the display of all date values.
If you want to display the date as a string with a particularly format then you can reverse the process with the to_char() function:
select to_char(to_date(to_char(col_date), 'YYYYMMDD'), 'YYYY-MM-DD')
from school
If you only want it reformatted as a string, and don't need it as a real date at all, you could just format the number directly:
select to_char(col_date, 'FM0000G00G00', 'nls_numeric_characters='' -''')
from school
But either way, only do that for final display - leave it as an actual date (not string) for any processing, joins, storage etc.
CodePudding user response：
Try converting int to varchar and then varchar to date
select cast(cast(col_date as varchar(10)) as date)