Date range for a calendar year

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

Transact-SQL (2000)

Date range for a calendar year

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-31 : 08:57:36

Morning,

I can't seem to figure this out. I need to present data from January to December of the current year

This is what I have so far:


select ExDate,LICTYPE
from dbo.vwExpiredRegTotal2
where DIVISIONCD = 'neweng'
and exdate between  January and December of the current year

I'm sure its easy I just can't seem to figure out how to make it dymanic.

Thanks for any help you can offer.

Laura

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 09:06:33

Try this

select ExDate,LICTYPE
from dbo.vwExpiredRegTotal2
where DIVISIONCD = 'neweng'
and month(exdate) between 1 and 12 and year(exdate)=yourYear

Madhivanan

Failing to plan is Planning to fail

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-31 : 09:33:20

Excellent! Thanks so much.

Laura

VIG
Yak Posting Veteran

86 Posts

Posted - 2005-08-31 : 16:21:54

select ExDate,LICTYPE
from dbo.vwExpiredRegTotal2
where DIVISIONCD = 'neweng'
and year(exdate)=year(getdate())

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-31 : 16:41:01

This is probably a better way to do it, since it would allow the query optimizer to use an index on column exdate, and it will use less CPU because you don't have to use the MONTH or YEAR function on column exdate from each row:


select
	ExDate,
	LICTYPE
from
	dbo.vwExpiredRegTotal2
where
	DIVISIONCD = 'neweng'	and
	exdate >= dateadd(yy,datediff(yy,0,getdate()),0)	and
	exdate <  dateadd(yy,datediff(yy,0,getdate())+1,0)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 01:27:22

Thats cool MVJ

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources