How select a date Range -- sql server programming

Ken
9/18/2006 10:39:53 PM

[quoted text, click to view]

WHERE
Convert(varchar(4),Year) + '0' + Convert(Varchar(2),Month) <= '200703'
AND
Convert(varchar(4),Year) + '0' + Convert(Varchar(2),Month) >= '200604'

It would have been better if you had a single int column in a table
called YearMonth so you could just say YearMonth <= 200703 and
YearMonth >= 200604

The reason is the query will run faster. If these fields were indexed
then the optimizer would do an index seek on them.

[quoted text, click to view]

hon123456
9/18/2006 11:32:19 PM

Thanks Ken,

Refer to the above question, how about the condition
when there is month 10 and
month11,and month 12.

Thanks

Simon Sabin
9/19/2006 12:00:00 AM

Hello Ken,

Or even

WHERE Year*100 + month between 200604 and 200703

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

[quoted text, click to view]
YearMonth>> = 200604
YearMonth>>
[quoted text, click to view]

Uri Dimant
9/19/2006 12:00:00 AM

Hi
CREATE TABLE #T
(
amount INT,
[month] INT,
[year]INT
)
INSERT INTO #T VALUES (10,4,2006)
INSERT INTO #T VALUES (20,5,2006)
INSERT INTO #T VALUES (30,6,2006)
INSERT INTO #T VALUES (40,1,2007)
INSERT INTO #T VALUES (10,2,2007)
INSERT INTO #T VALUES (40,1,2008)
INSERT INTO #T VALUES (10,2,2008)

SELECT * FROM #T
WHERE CAST(RTRIM([year]*10000+[month]*100+1) AS DATETIME) BETWEEN '20060401'
AND '20070101'

[quoted text, click to view]

Ken
9/19/2006 8:00:37 AM

sorry for the oversight. Hope you got the jest. Thanks for the other
solution.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : How select a date Range