Getting the next date - 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 2005 Forums

Transact-SQL (2005)

Getting the next date

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 10:47:32

I would like to have a SP in which I pass a month and a year (say 4 and 2007) and then I get the closest entry back looking forward in time.

So lets say I have a table with month and year column like so:
ID Month Year
5 10 2007
9 3 2008
10 6 2008

And I would pass it 4 and 2007, it would return the record with 10 and 2007, and if I would pass 5 2008 it would return the record with 6 2008

I assume this needs quite some looping, so I haven't got a clue how to do this in SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 10:52:18

[code]
declare @data table
(
ID int,
[Month] int,
[Year] int
)

insert into @data
select 5, 10, 2007 union all
select 9, 3, 2008 union all
select 10, 6, 2008

declare @month int,
@year int

select @month = 4,
@year = 2007

select top 1 *
from @data
order by abs(datediff(day,
dateadd(year, @year - 1900, dateadd(month, @month - 1, 0)),
dateadd(year, [Year] - 1900, dateadd(month, [Month] - 1, 0))))
[/code]

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 10:57:46

Wow... looks easier then I thought.
Thanks a lot.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 11:00:38

Not that complex actually.

As you have your month & year in separate column i have to combine both to form a datetime.
Which is done by dateadd(year, @year - 1900, dateadd(month, @month - 1, 0))

Next just compare the different in days and take the absolute value and ORDER it.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 11:03:18

It doen't really work though, if I enter 4 and 2007 it gives me back a record which has 2 and 2007, so perhaps it looks backward in time...
I gives back the closest date though, it's only not the closest date in the future

And could you explain the "- 1900" and the "month -1"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 11:06:06

oh you only wanted future date ?


select	top 1 *
from	@data
order by abs(datediff(day, 
		dateadd(year, @year - 1900, dateadd(month, @month - 1, 0)), 
		dateadd(year, [Year] - 1900, dateadd(month, [Month] - 1, 0))))

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 11:07:29

Very cool...

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 11:10:37

Still returning 2 2007 though....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 11:11:14

quote:
And could you explain the "- 1900" and the "month -1"

Sure.
dateadd(year, 2007 - 1900, 0) will add 2007 - 1900 which is 107 years to date 0. Date 0 in SQL is 1900-01-01. So it is adding 107 years to 1900 which will gives you 2007-01-01.

Same concept as the month - 1. Month starts from 1 not 0. So the dateadd(month, 4 - 1, 0) will convert to 1900-04-01.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 11:13:03

quote:
Originally posted by trouble2

Still returning 2 2007 though....


declare @data table
(
	ID 	int,
	[Month]	int,
	[Year]	int
)

insert into @data
select	 2, 10, 2007 union all
select	 6, 10, 2007 union all
select	 9,  3, 2008 union all
select	10,  6, 2008

declare @month	int,
	@year	int

select	@month 	= 4,
	@year	= 2007

select	top 1 *
from	@data
order by datediff(day, 
		dateadd(year, @year - 1900, dateadd(month, @month - 1, 0)), 
		dateadd(year, [Year] - 1900, dateadd(month, [Month] - 1, 0)))

ID          Month       Year        
----------- ----------- ----------- 
6           10          2007

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 11:19:19

What if you do:
insert into @data
select 2, 2, 2007 union all
select 6, 10, 2007 union all
select 9, 3, 2008 union all
select 10, 6, 2008

It should return:

ID Month Year
----------- ----------- -----------
6 10 2007

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-12 : 14:39:38

You can do this without converting to dates.


declare @year int
declare @month int

select @year = 2007, @month = 4

declare @t table ( ID int not null, Month int not null, Year int not null )

insert into @t
select id=5, Month=10, Year=2007 union all
select 9, 3, 2008 union all
select 10, 6, 2008

select
	*
from
	@t
where
	ID =
	(select
		ID=min(ID)
	from
		@t
	where
		(year*100)+ Month = 
		(
		select
			min((year*100)+ Month)
		from
			@t
		where
			(year*100)+ Month > (@year*100)+ @Month  ))


Results:

ID          Month       Year        
----------- ----------- ----------- 
5           10          2007

(1 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 15:19:24

[code]SELECT TOP 1
[Year],
[Month]
FROM @t
WHERE 12 * [Year] + [Month] >= 12 * @Year + @Month
ORDER BY [Year],
[Month][/code]

Peter Larsson
Helsingborg, Sweden

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-13 : 05:08:37

Hey Peter, that seems to work great...
Can you explain what you did?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 06:43:56

quote:

Originally posted by khtan

quote:
Originally posted by trouble2

Still returning 2 2007 though....


declare @data table
(
	ID 	int,
	[Month]	int,
	[Year]	int
)

insert into @data
select	 2, 10, 2007 union all
select	 6, 10, 2007 union all
select	 9,  3, 2008 union all
select	10,  6, 2008

declare @month	int,
	@year	int

select	@month 	= 4,
	@year	= 2007

select	top 1 *
from	@data
order by datediff(day, 
		dateadd(year, @year - 1900, dateadd(month, @month - 1, 0)), 
		dateadd(year, [Year] - 1900, dateadd(month, [Month] - 1, 0)))

ID          Month       Year        
----------- ----------- ----------- 
6           10          2007

Did you test with these?

select	@month 	= 12,
	@year	= 2007

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 06:45:13

quote:
Originally posted by Peso

SELECT TOP 1
[Year],
[Month]
FROM @t
WHERE 12 * [Year] + [Month] >= 12 * @Year + @Month
ORDER BY [Year],
[Month]

Peter Larsson
Helsingborg, Sweden

Is this correct?


SELECT		TOP 1
		[Year],
		[Month]
FROM		@t
WHERE		N * [Year] + [Month] >= N * @Year + @Month
ORDER BY	[Year],
		[Month]

Where N>=12

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 08:21:33

Yes. Any number >= 12 will do.
But 12 is the only number usable if you want to calculate differences and additions to dates with month and year only.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources