Archives of the TeradataForum

Message Posted: Wed, 09 Jan 2008 @ 21:04:40 GMT

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

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?

iv

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