Archives of the TeradataForum

Message Posted: Thu, 05 Jun 2008 @ 14:47:10 GMT

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

          > Sel (DATE '2007-05-10' - DATE '2007-04-28')Month;
          > Returns 0 prior to DR96278 (because the difference is not
          > a "full month"), but returns 1 after the fix is applied.

I wonder whether this is a really generally accepted convention.

A few test results on different RDBMS.

Oracle 9.2

     SELECT (DATE '2007-05-10' - DATE '2007-04-28') YEAR TO MONTH
     FROM dual

returns

     +00-00

Can understand this.

Oracle 10g

Had to give up on figuring out modifications close to the syntax in the original example. It would not accept the 9.2 syntax. The closest I could get working was

     SELECT extract ( MONTH FROM (DATE '2007-05-10' - DATE '2007-04-28') YEAR TO MONTH )
     FROM dual

It returns 0. Can understand why.

SQL Server 2005

     SELECT cast ('2007-05-10 00:00:00' as datetime )
              - cast ('2007-04-28 00:00:00' as datetime) month

is as close as I could get.

It returns this:

     1900-01-13 00:00:00.000

Hardly of any use to me.

Any thoughts or comments?

Victor

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