I need this Values

Question

I need this Values

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
March 4, 2009 at 2:16 am

#71869

Hi dear all,
If I give one date like '2008-03-04' i need the result next month last day or date

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

saravanantvr1984 (3/4/2009)
Hi dear all,
If I give one date like '2008-03-04' i need the result next month last day or date

SELECT DATEADD(M,1,'2008-03-04')

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

saravanantvr1984 (3/4/2009)
Hi dear all,
If I give one date like '2008-03-04' i need the result next month last day or date

Not quite sure I understand. Do you need the last day of the next month? So for '2008-03-04' you want '2008-04-30' (April has only 30 days)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 3

Try this

DECLARE @dt DATE

SET @dt = '2008-03-04'

SELECT DATEADD(D, -1, (CAST(YEAR(DATEADD(M,2,@DT)) AS VARCHAR)+'-'+ CAST(MONTH(DATEADD(M,2,@DT)) AS VARCHAR) + '-01'))

This will return the last date of next month.

Regards,
Nitin

Dugi SSCoach Points: 17998 More actions · Answer 4

nitinpatel31 (3/4/2009)
Try this
DECLARE @dt DATE
SET @dt = '2008-03-04'
SELECT DATEADD(D, -1, (CAST(YEAR(DATEADD(M,2,@DT)) AS VARCHAR)+'-'+ CAST(MONTH(DATEADD(M,2,@DT)) AS VARCHAR) + '-01'))
This will return the last date of next month.

Declaration of your variable here is wrong we are not in SQL Server 2008, please check the forum we are SQL Server 2000! So your code here will not work in SQL Server 2000 or SQL Server 2005! Sure that we must change the DATE data type to DATETIME!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 5

Dugi (3/4/2009)[hr
Declaration of your variable here is wrong we are not in SQL Server 2008, please check the forum we are SQL Server 2000! So your code here will not work in SQL Server 2000 or SQL Server 2005! Sure that we must change the DATE data type to DATETIME but I still no correct results!

Oops....:) It's my mistake. Dugi you are right, DATETIME should be used. But It gives the correct result with DATETIME. Where did you find the error in the script.?

Regards,
Nitin

Dugi SSCoach Points: 17998 More actions · Answer 6

nitinpatel31 (3/4/2009)
Dugi (3/4/2009)[hr
Declaration of your variable here is wrong we are not in SQL Server 2008, please check the forum we are SQL Server 2000! So your code here will not work in SQL Server 2000 or SQL Server 2005! Sure that we must change the DATE data type to DATETIME but I still no correct results!
Oops....:) It's my mistake. Dugi you are right, DATETIME should be used. But It gives the correct result with DATETIME. Where did you find the error in the script.?

Sorry man, now is correct little mistake during the test of your script! So sorry! But ok the declaration is not correct for this forum ( SQL Server 2000/2005)!

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 7

Dugi (3/4/2009)
Sorry man, now is correct little mistake during the test of your script! So sorry! But ok the declaration is not correct for this forum ( SQL Server 2000/2005)!
😉

It's okey buddy 🙂 ... Everybody makes mistakes... I made then you made. Now it is equal.... 😉

Regards,
Nitin

Dugi SSCoach Points: 17998 More actions · Answer 8

nitinpatel31 (3/4/2009)
Dugi (3/4/2009)
Sorry man, now is correct little mistake during the test of your script! So sorry! But ok the declaration is not correct for this forum ( SQL Server 2000/2005)!
😉
It's okey buddy 🙂 ... Everybody makes mistakes... I made then you made. Now it is equal.... 😉

:hehe: Yea correct we are equal now ... hahahah little joke!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

If the request is indeed for the last day of the following month, rather use this. Manipulating datetime using Dateadd and datediff is notably faster than converting pieces to string, putting the string together and then converting back to datetime

declare @dt datetime

set @dt = '2009/03/04'

SELECT DATEADD(dd,-1,dateadd(mm, datediff(mm,0, DATEADD(mm,2,@dt)),0))

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dugi SSCoach Points: 17998 More actions · Answer 10

GilaMonster (3/4/2009)
If the request is indeed for the last day of the following month, rather use this. Manipulating datetime using Dateadd and datediff is notably faster than converting pieces to string, putting the string together and then converting back to datetime
declare @dt datetime
set @dt = '2009/03/04'
SELECT DATEADD(dd,-1,dateadd(mm, datediff(mm,0, DATEADD(mm,2,@dt)),0))

Yes correct ... but the combination datediff and dateadd here are little bit confused, can you explain how to understand all these kind of manipulation with dateadd and datediff!? ( If it possible???)

Thnx,

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Same as any complex code. Deconstruct (or build it) piece by piece.

Broken down for explanation only:

DECLARE @dt datetime

SET@dt = '2009/03/04'

DECLARE @TwoMonthsFromNow DATETIME

DECLARE @StartOfMonthAfterNext DATETIME

DECLARE @EndOfNextMonth DATETIME

SET @TwoMonthsFromNow = DATEADD(mm,2,@dt)

SET @StartOfMonthAfterNext = dateadd(mm, datediff(mm,0, @StartOfMonthAfterNext),0) -- fairly standard trick, usually used to trim the time off a date.

SET @EndOfNextMonth = DATEADD(dd,-1, @StartOfMonthAfterNext)

SELECT @TwoMonthsFromNow, @StartOfMonthAfterNext, @EndOfNextMonth

For more datetime tricks - http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 12

GilaMonster (3/4/2009)
If the request is indeed for the last day of the following month, rather use this. Manipulating datetime using Dateadd and datediff is notably faster than converting pieces to string, putting the string together and then converting back to datetime
declare @dt datetime
set @dt = '2009/03/04'
SELECT DATEADD(dd,-1,dateadd(mm, datediff(mm,0, DATEADD(mm,2,@dt)),0))

Thanks Gila. This is great improvement over string manipulation. I have been doing string manipulation for many such cases. Your logic will help me a lot (Except I have to work more to modify my existing code 🙁 😉 ).

Thank you once again.

Regards,
Nitin

Dugi SSCoach Points: 17998 More actions · Answer 13

GilaMonster (3/4/2009)
Same as any complex code. Deconstruct (or build it) piece by piece.
Broken down for explanation only:
DECLARE @dt datetime
SET@dt = '2009/03/04'
DECLARE @TwoMonthsFromNow DATETIME
DECLARE @StartOfMonthAfterNext DATETIME
DECLARE @EndOfNextMonth DATETIME
SET @TwoMonthsFromNow = DATEADD(mm,2,@dt)
SET @StartOfMonthAfterNext = dateadd(mm, datediff(mm,0, @StartOfMonthAfterNext),0) -- fairly standard trick, usually used to trim the time off a date.
SET @EndOfNextMonth = DATEADD(dd,-1, @StartOfMonthAfterNext)
SELECT @TwoMonthsFromNow, @StartOfMonthAfterNext, @EndOfNextMonth
For more datetime tricks - http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/

Yes now understandable ... but these conversations are also with other functions sometime I confused what happened even if I'm going to deconstruct, anyway thnx for the quick explanation I will test some possibilities!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 14

Gail, I'm confused as to why to use the datediff function.

declare @dt datetime

set @dt = '1/31/2008'

-- 2 months out

-- Note that this adds 2 to the month and increments the year

-- if necessary. If the date is larger than the assigned month

-- the date is adjusted accordingly

select dateadd(m, 2, @dt) as twoMonth

-- Get the answer by

-- subtracting the date part from the date above

select dateadd(d, datepart(d, dateadd(m, 2, @dt)) * -1, dateadd(m, 2, @dt)) as answer