Find Number Falls Between two dates?

Question

Post reply

Find Number Falls Between two dates?

KMPSSS

Ten Centuries

Points: 1052
More actions
September 30, 2010 at 6:32 am

#387902

Hi,
I need to bring the result ,what r t months falls between two dates.
(i.e)
Eg.1
Declare @FrDate datetime,@ToDate datetime
Set @FrDate ='2010-01-31 17:38:58.577'
Set @ToDate ='2010-03-01 17:38:58.577'
need Output
Result
1
2
3
Eg.2 Suppose date is
Declare @FrDate datetime,@ToDate datetime
Set @FrDate ='2010-05-31 17:38:58.577'
Set @ToDate ='2010-09-01 17:38:58.577'
need Output
Result
5
6
7
8
9
Can anyone please guide me. how to bring this result

Viewing 6 posts - 1 through 5 (of 5 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Are you ever going to have something like:

Declare @FrDate datetime,@ToDate datetime

Set @FrDate ='2010-09-01 17:38:58.577'

Set @ToDate ='2011-02-01 17:38:58.577'

Where the years are crossed?

If so, would you want?

1

2

9

10

11

12

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KMPSSS Ten Centuries Points: 1052 More actions · Answer 2

KMPSSS

Ten Centuries

Points: 1052

September 30, 2010 at 6:50 am

#1229570

Right now we r calculating for only one year

WayneS SSC Guru Points: 95460 More actions · Answer 3

This is working for the sample data you provided. As previously mentioned, it won't work if you cross year boundaries.

Declare @FrDate datetime,@ToDate datetime

Set @FrDate ='2010-05-31 17:38:58.577'

Set @ToDate ='2010-09-01 17:38:58.577'

;WITH CTE AS

(

-- build a very small tally table - just need 12 rows, one for each month

SELECT TOP (12) N = ROW_NUMBER() OVER (ORDER BY (SELECT 0))

FROM sys.objects

)

-- get the months between (and including) the two supplied dates.

SELECT N

FROM CTE

WHERE N BETWEEN MONTH(@FrDate) and MONTH(@ToDate);

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KMPSSS Ten Centuries Points: 1052 More actions · Answer 4

Thanx friend. i got correct result. If possible give me result for cross boundary year.It's very useful for future

WayneS SSC Guru Points: 95460 More actions · Answer 5

KMPSSS (9/30/2010)
Thanx friend. i got correct result. If possible give me result for cross boundary year.It's very useful for future

How's this?

Declare @FrDate datetime,@ToDate datetime

Set @FrDate ='2010-09-28 17:38:58.577'

Set @ToDate ='2011-02-01 17:38:58.577';

-- See Jeff Moden's article

-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.

-- NOTE! A permanent tally table will always be MUCH faster

-- than this inline one. See the above article to create your own!

;WITH

Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT DISTINCT MONTH(DateAdd(month, N-1, @FrDate))--, DATEADD(month, N-1, @FrDate)

FROM Tally

WHERE N <= DATEDIFF(month, @FrDate, @ToDate)+1;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2