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)

for changing the datatype you need https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15 (example Changing the data type of a column) — Sergey, Apr 06 '22 at 14:23
Sorry I am very new to this I am using Dbeaver for sql statements and Exasol as my databse — Biryanii, Apr 06 '22 at 14:35

score 0 · Answer 1 · answered Apr 07 '22 at 07:34

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 ;

Changing Entire Column to Date Type in SQL

1 Answers1