Between... - 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)

Between...

Author

Topic

rsheppick
Starting Member

5 Posts

Posted - 2008-03-10 : 15:25:03

Greetings all...

I have 38,000 rows of date fields called Month, in a table called IntRevenue.....

Currently, I'm trying to do something simular to this however I cannot get it to work, the reason for this is a Report I am making...

select DATENAME(m, [Rev Month])
from IntRevenue
where DATENAME(m, [Rev Month]) between 'April' and 'May'

If I were to do April to April it shows me april fine, I guess it just doesn't know april is before may lol?

Likely it is me just being stupid...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 15:35:32

select DATENAME(m, [Rev Month])
from IntRevenue
where DATEpart(m, [Rev Month]) between 4 and 5

E 12°55'05.25"
N 56°04'39.16"

rsheppick
Starting Member

5 Posts

Posted - 2008-03-10 : 15:40:24

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'June' to data type int.

Month is DateTime

I guess because JUNE is char and I have ( Between 2 and 4 )

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-10 : 15:43:24

You didn't follow Peso's code close enough...

- Jeff
http://weblogs.sqlteam.com/JeffS

rsheppick
Starting Member

5 Posts

Posted - 2008-03-10 : 15:44:44

Doh, DATEPART

haha sorry.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-10 : 15:48:39

I strongly recommend that you read this:

http://weblogs.sqlteam.com/jeffs/archive/2007/09/14/sql-filter-by-month.aspx

To find better ways to filter for month ranges. You should not do what you are doing, since it will return all data within those months across all years, and it cannot make use of any indexes. A much better approach is to simply calculate the start date and the end date of the range of data you'd like to return, and use that as your filter.

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-10 : 17:40:36

As Jeff pointed out, it is better to filter a date range on a start and end date.

To select data for a particular month, do something like this:

declare @StartOfMonth		datetime
declare @StartOfNextMonth	datetime

set @StartOfMonth = '20070401'
set @StartOfNextMonth = dateadd(mm,1,@StartOfMonth)

select
	*
from
	MyTable
Where
	-- Select dates on or after start of month
	MyDateColumn >=  @StartOfMonth and
	-- Select dates before beginning of next month
	MyDateColumn <   @StartOfNextMonth

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 02:36:36

A kind hard to do, if he wants all Aprils and Mays for all years?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources