Date Diff with a diff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date Diff with a diff

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2003-07-03 : 03:24:13

I have 2 dates. eg: 10 Nov 2002 & 4 April 2003
I need a statement that will give me the following result:

11 | 2002
12 | 2002
01 | 2003
02 | 2003
03 | 2003
04 | 2003

Any ideas?
Thanks
Scott

Lady
Starting Member

32 Posts

Posted - 2003-07-03 : 03:59:16

declare @dtStart datetime
declare @dtEnd datetime
set @dtStart = 'June 6 2002 9:00PM'
set @dtEnd = 'June 6 2003 9:00PM'

declare @t table (i int, j int)

while @dtStart < @dtEnd
begin
insert into @t (i,j)
values(
datepart (m, @dtStart),
datepart(yy, @dtStart)
)
set @dtStart = dateadd(m, 1,@dtStart)
end
select * from @t

Edited by - Lady on 07/03/2003 03:59:59

macka
Posting Yak Master

162 Posts

Posted - 2003-07-03 : 04:48:12

And a set based solution might look something like this:


--declare datetime variables
DECLARE @d1 DATETIME
DECLARE @d2 DATETIME

--set the dates
SET DATEFORMAT DMY
SET @d1 = '10/11/2002'
SET @d2 = '4/4/2003'

--remove the day element of the date - this could be moved to the WHERE clause.
SET @d1 = DATEADD(d,-DATEPART(d,@d1)+1,@d1)
SET @d2 = DATEADD(d,-DATEPART(d,@d2)+1,@d2)

--DATEADD(yy,0,0) yields 01/01/1900 - so we can add years and months to give us a tally table of months/years
SELECT *
FROM (
	SELECT DATEADD(mm,m,DATEADD(yy,y,0)) as dt
	FROM
		(SELECT 1 m UNION SELECT 2 m UNION SELECT 3 m UNION SELECT 4 m UNION SELECT 5 m UNION SELECT 6 m UNION SELECT 7 m UNION SELECT 8 m UNION SELECT 9 m UNION SELECT 10 m UNION SELECT 11 m UNION SELECT 12 m) months
		CROSS JOIN 
		(SELECT 100 y UNION SELECT 101 y UNION SELECT 102 y UNION SELECT 103 y UNION SELECT 104 y ) years
	) s
WHERE dt BETWEEN @d1 AND @d2

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Scott
Posting Yak Master

145 Posts

Posted - 2003-07-03 : 05:21:50

Thanks all,
Anyone want to comment of the advantages of each?

Scott

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-04 : 02:04:03

Scott,

As with all things SQL, there is many ways to skin a cat...


DECLARE @d1 DATETIME
DECLARE @d2 DATETIME
SET @d1 = '20021110'
SET @d2 = '20030404'

SELECT DATEADD(m,Number,@d1)
FROM Numbers
WHERE DATEDIFF(mm,@d1,@d2) >= Number

The Numbers table is a table with a single column Number (Clustered PK) with values 0 to 1000. Create it in your model database and it will be available in each new database. Very handy...

As for advantages try them yourself....

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources