Casting Time in View

Database

Casting Time in View

Hi,

 

Below SQL works fine

SyntaxEditor Code Snippet

SEL CAST(092567 AS TIME) Test;

 But if I create view and submit Sel * <view>, I am getting "5407:   Invalid operation for DateTime or Interval" error.

 

View DDL:

SyntaxEditor Code Snippet

REPLACE VIEW TEST.TESView1
AS
SEL CAST(092567 AS TIME) Test123;

Below SQL fails with 5407 error:

 

Sel * from TEST.TESView1;

 

Database version: Teradata 15.00.0608  15.00.06.08

ODBC: ODBC 15.10.01.00

 

Any help would be appriciated.

 

Thanks


Accepted Solutions
Senior Apprentice

Re: Casting Time in View

This is no valid Teradata SQL syntax, it's based on some outdated ODBC-functionality treating TIME as INT, check the result of SEL TYPE(CAST(092567 AS TIME)) and what time is 09:25:67?  It will no longer work in newer releases of ODBC and never worked in .NET/CLI/etc.

 

But why do you want to do that?

The best way to write a time literal is TIME '09:25:59'

1 ACCEPTED SOLUTION
6 REPLIES
Senior Apprentice

Re: Casting Time in View

This is no valid Teradata SQL syntax, it's based on some outdated ODBC-functionality treating TIME as INT, check the result of SEL TYPE(CAST(092567 AS TIME)) and what time is 09:25:67?  It will no longer work in newer releases of ODBC and never worked in .NET/CLI/etc.

 

But why do you want to do that?

The best way to write a time literal is TIME '09:25:59'

Re: Casting Time in View

Oh, it was typo 

 

SQL is

SyntaxEditor Code Snippet

SEL CAST(092557 AS TIME(6)) Test;

I got result of below SQL as INTEGER

SyntaxEditor Code Snippet

SEL TYPE(CAST(092567 AS TIME))

 One of reason I got INTEGER result is in ODBC option I have selected Enable Legacy Parser, if I uncheck it then I get 5407 error for Sel Type...query.

Senior Apprentice

Re: Casting Time in View

Yep, the Legacy Parser should be disabled (and will no longer work in later releases), that's why it's called Legacy :-)

Re: Casting Time in View

Thanks dnoeth but how come with Legacy Parser Option SQL execute but failed to execute once it put inside view.

Teradata Employee

Re: Casting Time in View

A Select statement in a view is executed within the Teradata system by the Parsing Engine, not ODBC.  The PE cannot "see" the ODBC legacy parser switch on the client.

Re: Casting Time in View

Yes, GJColeman. Thanks for info.

 

Instade of use of TIME we changed view version to use Timetamp(0), it worked.