Doubt in query.........,

[andyharman]

How about

select * from MyTable
   where (sYear > 2007 or (sYear = 2007 and sMonth >= 6))
   and (sYear < 2008 or (sYear = 2008 and sMonth <= 6))

or

select * from MyTable
   where convert(varchar(6), (Year*100)+sMonth) between '200706' and '200806'

Regards Andy -- modified at 14:19 Wednesday 18th July, 2007

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

[Member 3879881]

Hi andy, Thank u for ur reply..., Ya i getting o/p for the first query..., But second query is not giving proper o/p..., Anyway one solution is enough..., Thank's lot..., keep in touch,

Regards, Magi

[RepliCrux]

5 votes to you man. I was about to say bad database design ;P

[N a v a n e e t h]

writing query for your requirement will be bit tough. Why don't you use date fields rather than storing date like this. In date fields you can use > or < operators to get the dates in between two dates.

---

My Website | Ask smart questions

[Member 3879881]

Thanks for ur reply navaneeth..., s navaneeth, i amn't getting date format from my end user..., Just i am getting month and year only..., So cant say in dateformat in my table..., But any way thanks for ur suggestion..., See andy's first query is giving solution for this probs..., Keep in touch, Friendly,

Regards, Magi

[andyharman]

You're welcome. The second query should have used (sYear*100). I hadn't woken-up properly:)

[Krish KP]

SELECT * FROM TblA WHERE Convert(Datetime, '01/'+sMonth+'/'+sYear, 103) BETWEEN '01-Jun-2007' AND '30-Jun-2008'

Regards KP

[ChandraRam]

andyharman wrote:

select * from MyTable where convert(varchar(6), (Year*12)+sMonth) between '200706' and '200806'

Actually, (Year*12)+sMonth wont get you those values at all ;)

[ChandraRam]

And looks like I wasn't reading properly either, before posting my message... pl ignore - I saw your clarification after I had hit the submit.

[RepliCrux]

You are right, the only thing is not all months have last day as 30, it can be 30, 28, 31... but well this is the right way to do anyway, there is a store procedure which gets the last day of the month in a particular year, can always use that.

[Krish KP]

given Month & Year are constant (june, 2007, 2008) Also the date which is derived is always 1st.

Regards KP