SQL DATEADD function

[Abdul N K]

I need help in T-SQL.

I am using DATEADD function and I want to add 6 months to a date. But
it does not return me the rusults, which I want

e.g. SELECT DATEADD(m,'2004 0630') returns 20041230
which is logical correct? But I want it to return end of month (i.e.
20041231)

Any help in this context will be highly appreciated

Abdul N. Khan

[Phil]

Re: SQL DATEADD function

Try this:

SELECT DATEADD(MONTH,D ATEDIFF(MONTH,3 0,'your date'),30)

e.g.

SELECT DATEADD(MONTH,D ATEDIFF(MONTH,3 0,'2-jan-1996'),30)
=============== =============== =============== ==
1996-01-31 00:00:00.000

This will return the last day of any month specified in 'your date',
so you should easily be able to add a further 6 months to the result of
this.

[Erland Sommarskog]

Re: SQL DATEADD function

Abdul N K (abdul_n_khan@h otmail.com) writes:[color=blue]
> I need help in T-SQL.
>
> I am using DATEADD function and I want to add 6 months to a date. But
> it does not return me the rusults, which I want
>
> e.g. SELECT DATEADD(m,'2004 0630') returns 20041230
> which is logical correct? But I want it to return end of month (i.e.
> 20041231)[/color]

I don't know your exact specification. This expression gives you the
last day of the month 6 months later if you are at the last day of
month:

declare @d datetime
select @d = '20040630'
SELECT dateadd(DAY, -1, dateadd(MONTH, 6, dateadd(DAY, 1, @d)))

However, for @d = 2004-12-30 you get 2004-06-29, which may not be
correct.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp