Date format automatically changed

Database

Date format automatically changed

Hi,

We have a view (below is the ddl) (view name is a)

REPLACE VIEW test.a  AS LOCK ROW FOR ACCESS 

SELECT * FROM test.b;

and below is the ddl fro table b (from where view is created)

CREATE MULTISET VOLATILE TABLE test.b,NO FALLBACK ,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO,

     LOG

     (

      MIN_TIME_ID DATE FORMAT 'YYYY-MM-DD')

PRIMARY INDEX ( MIN_TIME_ID )


now i am trying to create a volatile table as-

create volatile table c as (select * from test.a) with no data on commit preserve rows;

table is getting created successfully, but date format is automatically changed.

show table test.c --(c is volatile table)

CREATE MULTISET VOLATILE TABLE NACO_RADAR_PROD.abc1 ,NO FALLBACK ,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO,

     LOG

     (

      MIN_TIME_ID DATE FORMAT 'YY/MM/DD')

PRIMARY INDEX ( MIN_TIME_ID )

ON COMMIT PRESERVE ROWS;

Please help me, why this is happening.

2 REPLIES
Senior Apprentice

Re: Date format automatically changed

When a table is created using CREATE TABLE AS SELECT all options revert back to default, e.g. all columns are NULLable, all indices are lost.

In your case you can do a SET SESSION DATEFORM = ANSIDATE before or change you user's default MODIFY USER xxx AS  DATEFORM = ANSIDATE

Re: Date format automatically changed

Thank you so much.
It worked like charm :)