469,643 Members | 2,064 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

SQL DATEADD function

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,'20040630') 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
Jul 23 '05 #1
2 37411
Try this:

SELECT DATEADD(MONTH,DATEDIFF(MONTH,30,'your date'),30)

e.g.

SELECT DATEADD(MONTH,DATEDIFF(MONTH,30,'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.

Jul 23 '05 #2
Abdul N K (ab**********@hotmail.com) writes:
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,'20040630') returns 20041230
which is logical correct? But I want it to return end of month (i.e.
20041231)


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Zlatko Matić | last post: by
4 posts views Thread by ey.markov | last post: by
1 post views Thread by C.Davidson | last post: by
reply views Thread by Zlatko Matić | last post: by
2 posts views Thread by Rich Raffenetti | last post: by
3 posts views Thread by Mel | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.