Problem with an insert statement...

Database
Enthusiast

Problem with an insert statement...

CREATE VOLATILE TABLE temp(cola VARCHAR(20),colb VARCHAR(20),dat1 DATE FORMAT 'ddmmyyyy',

dat2 DATE FORMAT 'ddmmyyyy')

 ON COMMIT PRESERVE ROWS;

When i tried inserting using the ODBC connection like this

INSERT INTO temp VALUES('a','b','01012000','05012000');  

its working.

But when i tried running the same in sert 

INSERT INTO temp VALUES('a','b','01012000','05012000');

in teradata.net connection in queryman

Its throwing an error!!!

What am i missing here??

5 REPLIES
Supporter

Re: Problem with an insert statement...

most likely the date conversion fails - what is your error message?

Enthusiast

Re: Problem with an insert statement...

U r correct 'A character string failed conversion to a numeric vale'.  But why? Its working in ODBC and why not in Teradata.Net

Supporter

Re: Problem with an insert statement...

I guess .net is handling conversions differently as ODBC.

But as you have a date column you should be save if you convert your string explicitly into a date in the insert. This will prevent you also from errors or the need to change code in case someone decides to change the date format in the table.

INSERT INTO temp VALUES('a','b',cast('01012000' as date format'ddmmyyyy'),cast('05012000' as date format'ddmmyyyy'));

Enthusiast

Re: Problem with an insert statement...

Yea you query works fine with Teradata.net connection... 

Enthusiast

Re: Problem with an insert statement...

Thanks... Can you please tell me what are the major differences between a ODBC and Teradata.net connections..