October 12, 2005 at 5:03 am
Hello
I have a table with a column for a month and another for a year.
I want to search for a given mm/yyyy between mm1/yyyy1 and mm2/yyyy2
How can I do that?
Thank you for any help.
Augusto
October 12, 2005 at 5:47 am
You will have to put the columns back together in a dateformat to search like that along with the MM/YYYY you are looking for i.e.
WHERE MM/01/YYYY BETWEEN MM1 + '/01/' + YYYY1 AND MM2 + '/01/' + YYYY2
May need to do CAST or CONVERT to accomplish this to make them all CHAR fields. May need to CAST to DATETIME as well after that or not.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 12, 2005 at 7:12 am
Of course if the columns are char(4) and char(2) (and I would not be surprised if they are) then you could simply do:
WHERE yearcolumn + monthcolumn BETWEEN '200501' AND '200510'
The month column would of course need to be left-padded for this to work.
October 14, 2005 at 12:57 am
Hi there
This is basically what AJ also said... Try this:
select * from TblName
where
convert(smalldatetime, cast(MonthField as varchar(2) + '/' + cast(YearField as varchar(2) + '/' + '01',111)
between
'2005/04/01' --Date1
and '2005/08/01' --Date2
<hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,
Roelof
<a href='http://' class='authorlink' target='_blank'></a>
--There are only 10 types of people in the world. Those who understand binary, and those who don't.
October 14, 2005 at 1:17 am
But this query would not be able to use an index seek to find results. A where clause must be in a SARG format, that is column operator constant. The convert for the column turns it into an expression, and thereby the caluse is not a SARG anymore.
October 14, 2005 at 7:25 am
I used Chris Hedgate solution and it does what I wanted it to do.
Thank you all for your support.
Augusto
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply