Changing Entire Column to Date Type in SQL

Question

I am trying to convert the datatype of a column Confirmation_Dateto date type but unable to to do so, the Column is of varchar type and SER_MUHAMMAD_MALLICK.user is the tablel. Iam using the following code snippet.

SELECT CONVERT (datetime, Confirmation_Date , 104) FROM USER_MUHAMMAD_MALLICK.user_upsells;

I am receiving the following error message when I run this code

SQL Error [42000]: syntax error, unexpected IDENTIFIER_LIST_ [line 1, column 17] (Session: 1729360210797461536)

Answer 1

You can use alter table modify column. For example to convert a VARCHAR column to DATE:

create schema s;
create table t(c varchar(2000000));
insert into t values '2020-01-01', '2021-05-30';

/*
COLUMN_NAME SQL_TYPE              NULLABLE DISTRIBUTION_KEY PARTITION_KEY 
----------- --------------------- -------- ---------------- ------------- 
C           VARCHAR(2000000) UTF8 TRUE     FALSE            FALSE         
*/
describe t;
alter table t modify column c date ;
/*
COLUMN_NAME SQL_TYPE NULLABLE DISTRIBUTION_KEY PARTITION_KEY 
----------- -------- -------- ---------------- ------------- 
C           DATE     TRUE     FALSE            FALSE         
*/
describe t;

Note that the varchar values must be of the correct date format. If you have a different date format you need to specify the NLS_DATE_FORMAT session parameter with ALTER SESSION:

create or replace table t(c varchar(2000000));
insert into t values '01.01.2020', '30.05.2020';
-- data exception - invalid character value for cast; Value: '01.01.2020' Format: 'YYYY-MM-DD'
alter table t modify column c date ;

alter session set NLS_DATE_FORMAT='dd.mm.yyyy';

-- works now
alter table t modify column c date ;