Posted by Erland Sommarskog on 12/31/06 14:50

--CELKO-- (jcelko212@earthlink.net) writes:
>>> In ANSI SQL, getdate() should be CURRENT_TIMESTAMP. Whether dateadd (my
query mistakely had "datedadd" I see now) is in ANSI SQL, I don't know, nor
do I know about date aritmethics in general in ANSI SQL. <<
>
> It is not ANSI/ISO. Temporal math in Standard has infixed operators +
> and - with temporal unit declarations, EXTRACT() and oher functions
> that are very different from the Sybase/SQL Server "code museum"
> function calls. DB2 and Oracle 10 now both have the proper syntax. I
> thnk that Mimer and Solid are also up to standards.

SQL Server also has +/- for datetime, although I suspect this is due
to implicit conversion. Look at this:

declare @d1 datetime, @d2 datetime
select @d1 = '19820223 16:21:32', @d2 = '19871031 12:23:23'
select @d1 + 1, @d2 - @d1

Gives:

1982-02-24 16:21:32.000 1905-09-07 20:01:51.000

The first makes perfect sense, the second is just rubbish.

The dateadd() etc stuff is not that pretty, but the get the job done.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]