Snowflake SQL error: Insert query: expecting DATE but got NUMBER(9,0) for column

0
votes

SQL compilation error: Expression type does not match column data type, expecting DATE but got NUMBER(9,0) for column Invoice_Date

I tried TO_DATE(Invoice_Date) and DATE(Invoice_Date), but still get the same error... Please let me know if there is something else I can try.

Here's the format of the SQL:

INSERT INTO UBS_STAGE.STAGE_BILLING
(
    INVOICE_DATE
)
SELECT INVOICE_DATE
FROM BSCHEMA.BTABLE;

The column type in BTABLE is DATE and sample data is as follows:

2019-05-7
2019-05-22
3
sqlsnowflake-cloud-data-platform
There is not enough information to answer your question. Edit your question and provide an example of the SQL you are running and some sample data and expected results – Simon D

3 Answers

0
votes

If you are passing in a number, you need to cast it to a string first, and then tell Snowflake what the format of the date is. So, something along these lines (making assumptions on your number format):

DATE(numberfield::varchar,'YYYYMMDD')
0
votes

I think you can just cast this as a date:

select column1::date 
from values ('2019-05-7'),('2019-05-22');

Returns:

COLUMN1::DATE
2019-05-07
2019-05-22
0
votes

are the datatype of the columns Date in both the tables, because i tried and this worked for me.

create table STAGE_BILLING ( INVOICE_DATE date)
insert into STAGE_BILLING values(current_date())
select * from STAGE_BILLING
create table BTABLE(INVOICE_DATE  date);
INSERT INTO STAGE_BILLING ( INVOICE_DATE ) SELECT INVOICE_DATE FROM BTABLE; 
select * from STAGE_BILLING