How to Compare dates - 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)

How to Compare dates

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-01-22 : 10:53:57

I am having a table with the following structure:

Id(int) Name(varchar) Desc(varchar) Charge(money) Validuntil(datetime)
1 PE Some desc 995 1/30/2007 12:00:00 AM
2 PE Some desc 1100 6/30/2008 12:00:00 AM
3 PE Some desc 1500 12/30/2008 12:00:00 AM

Now , I want to get the charge based on the Validuntil date. Like, When I use the query with date 1/23/2007, then I should get 995 as charge and when i query with date 5/30/2007, then i should get 1100 as charge and when i query with date 7/30/2007, i should get 1500 as charge.
How should i write query so that I get the desired result.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-22 : 10:56:56

select charge
from tbl
where Validuntil = (select max(Validuntil) from tbl where ValidUntl <= @mydate)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 10:57:52

select top 1 *
from yourtable
where validuntil >= @wanteddate
order by validuntil

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 11:02:40

nr, your query does not work for today's date.

-- prepare sample data
declare	@s table ([id] int, [name] varchar(20), [desc] varchar(20), [charge] money, [validuntil] datetime)

insert	@s
select	1, 'PE', 'Some desc', 995, '1/30/2007 12:00:00 AM' union all
select	2, 'PE', 'Some desc', 1100, '6/30/2008 12:00:00 AM' union all
select	3, 'PE', 'Some desc', 1500, '12/30/2008 12:00:00 AM'

declare	@mydate datetime,
	@wanteddate datetime

select	@mydate = getdate(),
	@wanteddate = getdate()

-- nr original
select charge
from @s
where Validuntil = (select max(Validuntil) from @s where ValidUntil <= @mydate)
 
-- nr improved by peso
select charge
from @s
where Validuntil = (select min(Validuntil) from @s where ValidUntil > @mydate)

-- peso
select top 1 *
from @s
where validuntil >= @wanteddate
order by validuntil

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-22 : 11:16:53

oops thanks - misread the question.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-01-22 : 11:29:23

Thanks all for your help.My problem is solved by queries given by you.Peso, i am very impressed by your way of generating queries for problems.thanks for helping.Can you provide any link where i get to practise SQL like having questions to build a query. I am aware of sql but finds myself wanting in using it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 11:47:53

Hang around here at SQLTeam and learn more.
And when you are ready, provide some suggestion to a solution yourself.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources