Specific Search within 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 2000 Forums

Transact-SQL (2000)

Specific Search within date

Author

Topic

zefiros
Starting Member

16 Posts

Posted - 2008-02-27 : 04:40:20

Hi,

I have a column which is of type smalldatetime.

I want to be able to search in a range within dates. For instance

"select all rows that year is 2008"
or
select all rows that year is 2008 and month is Feb"

How can i do this since in the smalldatetime format since days-months-year are embeded?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 04:49:22

Try using MONTH() & YEAR() date functions

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-27 : 04:53:11

[code]-- for year 2008
select * from table where datecol >= '20080101' and datecol < '20090101'

-- for year and month
select * from table where datecol >= '20080201' and datecol < '20080301'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-27 : 05:26:49

If you want to pass them as parameters then


1 
Select 
	columns 
from 
	yourtable 
where 
	datecol>= dateadd(year,@year-1900,0) and 	datecol< dateadd(year,@year-1900+1,0) 

2

Select 
	columns 
from 
	yourtable 
where 
	datecol>= dateadd(month,@month-1,dateadd(year,@year-1900,0)) and 
	datecol< dateadd(month,@month,dateadd(year,@year-1900,0))

Madhivanan

Failing to plan is Planning to fail

zefiros
Starting Member

16 Posts

Posted - 2008-02-28 : 03:53:07

thank u all!I have chosen the simplest way since i don't want to perform something complicated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 04:36:39

quote:
Originally posted by zefiros

thank u all!I have chosen the simplest way since i don't want to perform something complicated

Can you post your simplest way?

Madhivanan

Failing to plan is Planning to fail

zefiros
Starting Member

16 Posts

Posted - 2008-02-28 : 17:43:55

I meant harsh_athalye way as the simpler way and not use functions since I am using it inside java code and to generate the query the process goes through loops and other validation rules. Using harsh_athalye will be simpler for the other developers on the team to understand and build since it does the job done

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources