Archives of the TeradataForum
Message Posted: Wed, 09 Jan 2008 @ 21:04:40 GMT
Sometimes I enjoy interestingly weird glitches like this...
Ok, so you've decided to drink the kool-aid, that you must use intervals instead of the good old Teradata functions, so you go to town and do something like:
select date - interval '6' month
Well, guess what, fellow Teradatyls? This will fail several times a year, as in:
select CAST('2007-12-31' AS DATE) - interval '6' month
Which results in an ERROR massage (that's what you get at a spa when you get an error) of 'Invalid date. (2665)'. As opposed to the good old Teradata function:
select ADD_MONTHS('2007-12-31', -6)
Which results in the value '2007-06-30'. Why, you may ask? Because the ANSI date interval @$%$# function just subtracts six from the month, which results in '2007-06-31'. Which turns out to only be a valid date on the planet Mars. Anybody else think this is dumb? Oh yeah, I know, somebody's going to say, "But the ANSI spec says that's what it's supposed to do?" I can hear the voice of my father saying, "If everybody's jumping off a cliff, does that mean that you have to do it to?
And another rant! Why does a CAST of a TIMESTAMP(6) to a TIMESTAMP(<6) fail with an error of 'DateTime field overflow. (7454)', so you end up doing something stupid like:
CAST(CAST(Ap2K_Sect_Crtn_Tmstp AS CHAR(19)) AS TIMESTAMP(0)
Wait! Let me guess. It's a FEATURE of the ANSI SQL standard. WE WOULDN'T WANT OUR CODE TO WORK ALL THE TIME, WOULD WE?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|