sql server 2000 start date - end date 2

[duchovnick]

Hi !
i' d like to limit the scope of my select query between 2 dates: maxdate and mindate. But i'm interseted only at the month part of the date. somethink like:

select * 
from
mytable
where
mydate(mm) > (mm,01/01/2006) and
mydate(mm) < (mm,01/01/2007)

mm is to indicate the month part of the date expression.
Could anyone please help me with that ?
Thanks !

 

[SgtJarrow]

Month(date) is a function you should look at....

BUT, if you are going to search for a month-only between two dates and the table has more than one year of data in it, you will find that the results return each month, no matter the year.

So if you table has 1/1/06 and 1/1/07, they will both be returned if you search against just Month(date).

Do you want to select a group of records between two dates, but GROUP or only show the Month portion of the date??? If so:

Select Month(mydate), * from myTable Where mydate Between '1/1/2006' and '12/31/2006'

should do the trick.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[sqlcasey]

select *
from
mytable
where
datepart(month, mydate) > 1
and
datepart(month, mydate) < 3
and
datepart (year, mydate) = 2006

-- this would return all dates with a month of February, or month = 2, in the year of 2006

-- see datepart in BOL

 

[bborissov]

You can't be interested ONLY at the month part. With this query you will get ALL records that have January as a month, no matter what year they have. Can you post some example records and the desired result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Gixonita]

Hi,

Your requirement doesnt make sence:

(mm,01/01/2006) --> mm = 1 (January)
(mm,01/01/2007) --> mm = 1 (January)

So basically your where asks for:

mydate(mm) > 1 and mydate(mm) < 1

Which will return no rows.

I would just go with the whole date and using BETWEEN:

select *
from
mytable
where
mydate(mm) BETWEEN '01/01/2006' and '01/01/2007'

Hope this helps

 

[duchovnick]

Thanks a lot mstrmage1768 for giving me an aid and for thinkink further then i did in regard to my problem...
and i'd like to cordially thank you sqlcasey in showing me how to do it right and different..
Relating to your question bborissov: i need to organize clients on month's basis.
i need to show all clients that made orders in January then February etc..
I didnt know how to write the query to make the scope of each select statement on month's basis.
Thanks a lot to all of you !

 

[duchovnick]

Hi Gixonita
You used my code to show me where i'm wrong and unfortunately its hard for me to understand the code i wrote which was wrong from the beginning but i thank you for trying to help...