Archives of the TeradataForum
Message Posted: Fri, 01 Sep 2006 @ 19:46:21 GMT
| Subj: | | Re: ODBC Ansi vs Integer Dates |
| |
| From: | | Dieter Noeth |
Eric Barner wrote:
| | I agree that this is equivalent : | |
> 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 xxx
| | BUT... it gives me the same error :( | |
I tried both versions on ODBC 3.05.00.04 and both worked with different "DateTime Format" settings and "Allow Use of ODBC SQL Extensions in
Queries" on or off.
As a test add that cast to the WHERE-condition and check the explain text...
| | The reason for formatting to CHAR(19) is for truncating the precision. Killing the milliseconds. | |
I know
| | I tried to directly cast to Timestamp(0) from the tstamp1 field(which is timestamp(6)). | |
No, it's "7454 DateTime field overflow"
| | And I tried to use cast (Current_time as timestamp(6)) even though the TYPE of current_time is TIME(0) WITH TIME ZONE ,just to see if
it was a data length issue with odbc. | |
| | To my surprise the same type of operation worked. | |
Because it's only allowed to increase accuracy, but not decrease. Don't ask my why it's that restrictive, blame ANSI SQL for it :-(
| | I didn't quite understand your last statement | |
| | "It's usually preventing those Time/Timestamp related problems with ODBC. But of course you still have to check your
queries..." | |
| | Did you mean that people change to AIA or AAA, AII to get away from these issues? | |
Yes.
Try "select current_time(x)" with any x > 0 and TimeFormat set to I. If it's AAA, then these issues are gone, but others might show up :-
)
For me AAA is the recommended setting, just like "Allow Use of ODBC SQL Extensions in Queries" should always be set to OFF.
| | I would agree, but I am concerned with having adverse effects by changing to Ansi (date math or similar things) | |
Date math shouldn't change, but Time/Timestamp might.
| | Any thoughts or experience with changing to ANSI mode? | |
The main point is to use the same ODBC settings for all users...
Dieter
| 
