How to Get Month Using Given two Dates

Question

How to Get Month Using Given two Dates

farooq.hbs

SSCrazy

Points: 2083
More actions
October 31, 2011 at 3:33 am

#148736

Hi All,
I need a help on date Diff Concept..
I want to get the Month Number of Given two dates
Ex: 02-Feb-2011 to 25-Feb - 2011
Result: 2
( 2 , Because, Feb Is second Month)

Viewing 10 posts - 1 through 9 (of 9 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

farooq.hbs (10/31/2011)
Hi All,
I need a help on date Diff Concept..
I want to get the Month Number of Given two dates
Ex: 02-Feb-2011 to 25-Feb - 2011
Result: 2
( 2 , Because, Feb Is second Month)

Need you to be a bit clearer. What if your two dates are different months, what would you want to be returned then?

For now, this might help

DECLARE @date DATETIME

SET @date = '02-Feb-2011'

SELECT DATEPART(mm,@date)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

farooq.hbs SSCrazy Points: 2083 More actions · Answer 2

Thnks for u r Reply

Now if the date is from two Diff Month 02-Feb-2011 to 03 - Mar-2011

how to get this following resuklt

Ans: 2

3

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

You're talking about only two dates?

If yes, the following should work for you:

DECLARE @STRING VARCHAR(500),

@DATE1 DATETIME,

@DATE2 DATETIME;

SELECT @DATE1 = '20110203',

@DATE2 = '20110405';

SELECT @STRING = ISNULL(CAST(MONTH(@DATE1) AS VARCHAR(2)) + ',', ''),

@STRING = @STRING + CAST(MONTH(@DATE2) AS VARCHAR(2));

SELECT @STRING;

Or do you mean all possible months between two dates?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

farooq.hbs SSCrazy Points: 2083 More actions · Answer 4

farooq.hbs

SSCrazy

Points: 2083

October 31, 2011 at 4:28 am

#1400985

Hi codebyo,

Thanks for ur Posting..I got My result

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 5

No problem. 🙂

If you need all possible months between two dates you could use something like this:

DECLARE @STRING VARCHAR(500),

@DATE1 DATETIME,

@DATE2 DATETIME,

@MONTHDIFF INT;

SELECT @DATE1 = '20110203',

@DATE2 = '20110905';

SET @MONTHDIFF = MONTH(@DATE1);

WHILE @MONTHDIFF <= MONTH(@DATE2)

BEGIN

SELECT @STRING = ISNULL(@STRING + ',', '') + CAST(@MONTHDIFF AS VARCHAR(2)),

@MONTHDIFF = @MONTHDIFF + 1;

END;

SELECT @STRING;

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

codebyo (10/31/2011)
No problem. 🙂
If you need all possible months between two dates you could use something like this:
DECLARE @STRING VARCHAR(500),
@DATE1 DATETIME,
@DATE2 DATETIME,
@MONTHDIFF INT;
SELECT @DATE1 = '20110203',
@DATE2 = '20110905';
SET @MONTHDIFF = MONTH(@DATE1);
WHILE @MONTHDIFF <= MONTH(@DATE2)
BEGIN
SELECT @STRING = ISNULL(@STRING + ',', '') + CAST(@MONTHDIFF AS VARCHAR(2)),
@MONTHDIFF = @MONTHDIFF + 1;
END;
SELECT @STRING;
Best regards,

No need for a loop here.

DECLARE @DATE1 DATETIME,

@DATE2 DATETIME,

@MONTHDIFF INT;

SELECT @DATE1 = '20110203',

@DATE2 = '20110905';

SELECT STUFF((SELECT ',' + N

FROM (SELECT CONVERT(VARCHAR(2),N) AS N

FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL

SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8

UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL

SELECT 11 UNION ALL SELECT 12) tally(N)

WHERE N >= DATEPART(mm,@DATE1) AND N <= DATEPART(mm,@DATE2)

) a

FOR XML PATH(''), TYPE).value('.[1]','varchar(1000)'), 1, 1, '');

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

Thank you for the alternative.

I only posted the code that way because we're only dealing with variables and not result sets, so no need to worry about loops there and also because it's easier to read. 😉

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

codebyo (10/31/2011)
... so no need to worry about loops there and also because it's easier to read. 😉

You do understand that them's fightin' words, right Andre? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 9

Jeff Moden (10/31/2011)
codebyo (10/31/2011)
... so no need to worry about loops there and also because it's easier to read. 😉
You do understand that them's fightin' words, right Andre? 😉

I meant that in comparison with the derived tally table being used in a FOR XML PATH with a string replacement routine. But you got me there. I somewhat feel ashamed of having stated that. :hehe:

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA