Archives of the TeradataForum

Message Posted: Fri, 01 Sep 2006 @ 17:37:56 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: ODBC Ansi vs Integer Dates

From:		Dieter Noeth

Eric Barner wrote:

Currently we have some canned, custom SQL reports that use timestamps as part of a join. They are of data type TIMESTAMP(6). However, the level of the join needs to happen at a TIMESTAMP(0) level.

So why is the datatype TIMESTAMP(6)?

Let me guess, you got a real time operating system with an accuracy up to a millionth of a second ;-)

If it's assigned by Teradata it's accuracy is TIMESTAMP(2)

So. The we do the following in the join condition.

          > Tstamp1  is TIMESTAMP(6).
          >
          > Where ...
          >
          > and
          >         Cast((cast tstamp1 as char(19)) as timestamp(0))
          >         BETWEEN  Cast((cast tstamp2 as char(19)) as timestamp(0))
          >                   AND Cast((cast tstamp3 as char(19)) as timestamp(0)).

I know this isn't clean because the Timestamp(6) is really 26 in length. I tried to substring it properly, but ended up with the same result.

I can make this scenario work with dbc tables,

Because those are TIMESTAMP(0)

          > or cast(CURRENT_TIME as
          > timestamp(0)) with no error.

Because CURRENT_TIME is CURRENT_TIME(0) by default :-)

...

My two questions are:

1) Has anyone encountered this type of problem and found a workaround for the above example without changing ODBC driver settings.

If it's just for the join, why do you recast to a Timestamp again?

     cast (tstamp1 as char(19))
     BETWEEN  Cast(tstamp2 as char(19))
               AND Cast(tstamp3 as char(19))

should yield the same result

2) Has anyone switched to AII or AAA or AIA from IIII and had any issues, problems, or strange effects.

It's usually preventing those Time/Timestamp related problems with ODBC. But of course you still have to check your queries...

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference