SQL dateadd

25 Nov 2005 7:21 PM

Dave

SELECT DATEADD(month, 1, '2005-05-31')
GO

SELECT DATEADD(month, 1, '2007-01-31')
GO

SELECT DATEADD(month, 1, '2008-01-31')
GO

SELECT DATEADD(month, 1, '2005-06-30')
GO

All of the above gives me the expected result except for the last one.
The last select statement returns '2005-07-30', whereas I am expecting
'2005-07-31'.

Can someone please explain?

25 Nov 2005 7:41 PM

Raymond D'Anjou

How can SQL know that you want the LAST day of the next month and not the
30th?
SQL server had to make certain choices/assumptions and I agree with them on
this one.

If you need to find the last days of each month, a calender table is your
friend: :-)
http://aspfaq.com/show.asp?id=2519

Show quote

"Dave" <D***@discussions.microsoft.com> wrote in message
news:1460E30E-4252-40F0-A5FC-E9865B28FB92@microsoft.com...
> SELECT DATEADD(month, 1, '2005-05-31')
> GO
>
> SELECT DATEADD(month, 1, '2007-01-31')
> GO
>
> SELECT DATEADD(month, 1, '2008-01-31')
> GO
>
> SELECT DATEADD(month, 1, '2005-06-30')
> GO
>
> All of the above gives me the expected result except for the last one.
> The last select statement returns '2005-07-30', whereas I am expecting
> '2005-07-31'.
>
> Can someone please explain?

25 Nov 2005 10:29 PM

ML

The 30-day month also corresponds to the principles of international law,
based upon the principles from the Roman law (i.e. <i>tempus civile</i>),
constituting yet another element of SQL that is over 2000 years old. :)

ML

25 Nov 2005 10:05 PM

Hugo Kornelis

On Fri, 25 Nov 2005 11:21:05 -0800, Dave wrote:

Show quote

>SELECT DATEADD(month, 1, '2005-05-31')
>GO
>
>SELECT DATEADD(month, 1, '2007-01-31')
>GO
>
>SELECT DATEADD(month, 1, '2008-01-31')
>GO
>
>SELECT DATEADD(month, 1, '2005-06-30')
>GO
>
>All of the above gives me the expected result except for the last one.
>The last select statement returns '2005-07-30', whereas I am expecting
>'2005-07-31'.
>
>Can someone please explain?

Hi Dave,

As Raymond indicates - how would SQL Server know if '2005-06-30' means
"day 30 of a given month" or "last day of a given month"? You wouldn't
expect DATEADD(month, 1, '2005-06-29') to return '2005-07-30', do you?

To get last day of next month, try something like:

SELECT DATEADD(day, -1, DATEADD(month, 1, DATEADD(day, 1, '20050630')))

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)