How to limit date Range

8 Sep 2006 7:27 AM

hon123456

Dear all,

I want to limit a select sql statement for specific month.
e.g. for Jan it is : select * from table01 where tradedate >=
'01/01/2006' and tradedate <= '01/31/2006'
For Feb it is : select * from table01 where tradedate >= '02/01/2006'
and tradedate <='02/28/2006'

I cannot use '02/31/2006' for Feb. The SQL server will complain out of
range. That means, if I have twelve months I need to type 12 sql
statement. Is there any general function or method that I can use to
limit the date range and do it in one sql statement with proper
variable or function?

Thanks

8 Sep 2006 8:01 AM

ML

declare @month tinyint

set @month = 2

select ...
         from ...
         where (tradedate >= cast((cast(year(getdate()) as char(4)) +
right('0' + cast(@month as varchar(2)), 2) + '01') as datetime))
                   and (tradedate < dateadd(mm, 1,
cast((cast(year(getdate()) as char(4)) + right('0' + cast(@month as
varchar(2)), 2) + '01') as datetime)))

ML

---
http://milambda.blogspot.com/

8 Sep 2006 8:51 AM

Sha Anand

use

WHERE YEAR(tradedate) = 2006 AND MONTH(tradedate) = 1

- Sha Anand

Show quote

"hon123456" wrote:

> Dear all,
>
> I want to limit a select sql statement for specific month.
> e.g. for Jan it is : select * from table01 where tradedate >=
> '01/01/2006' and tradedate <= '01/31/2006'
> For Feb it is : select * from table01 where tradedate >= '02/01/2006'
> and tradedate <='02/28/2006'
>
> I cannot use '02/31/2006' for Feb. The SQL server will complain out of
> range. That means, if I have twelve months I need to type 12 sql
> statement. Is there any general function or method that I can use to
> limit the date range and do it in one sql statement with proper
> variable or function?
>
> Thanks
>
>

8 Sep 2006 10:44 AM

ML

Sadly this will not let the optimizer use any indexes on the datetime column.

ML

---
http://milambda.blogspot.com/

8 Sep 2006 3:47 PM

Tracy McKibben

hon123456 wrote:
Show quote

> Dear all,
>
> I want to limit a select sql statement for specific month.
> e.g. for Jan it is : select * from table01 where tradedate >=
> '01/01/2006' and tradedate <= '01/31/2006'
> For Feb it is : select * from table01 where tradedate >= '02/01/2006'
> and tradedate <='02/28/2006'
>
> I cannot use '02/31/2006' for Feb. The SQL server will complain out of
> range. That means, if I have twelve months I need to type 12 sql
> statement. Is there any general function or method that I can use to
> limit the date range and do it in one sql statement with proper
> variable or function?
>
> Thanks
>

DECLARE @TargetMonth INT
DECLARE @TargetYear INT

SELECT @TargetMonth = 2
SELECT @TargetYear = 2006

SELECT *
FROM Table1
WHERE TradeDate BETWEEN
   CONVERT(DATETIME,
      CONVERT(CHAR(4), @TargetYear)
      + RIGHT('00' + RTRIM(CONVERT(CHAR(2), @TargetMonth)), 2)
      + '01')
   AND
   CONVERT(DATETIME,
      CONVERT(CHAR(4), @TargetYear)
      + RIGHT('00' + RTRIM(CONVERT(CHAR(2), @TargetMonth + 1)), 2)
      + '01') - 1

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

8 Sep 2006 4:41 PM

newscorrespondent

Code the 12 in a function and use it everywhere.

There is a copy/paste function in your editor it is not that much work.

or
where year(tradedate) = @desiredyear
and month(traddate) = @desiredmonth