Case in Where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case in Where clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-09 : 08:24:04

Zack writes "Hi Folks:

I am trying to write a dynamic where clause.
What I want it to do is if the system month is greater then July
then I want all data between 01/01/04 to 06/30/05, else
I want all data between 01/01/03 to 06/30/04
The following is my sql

select red_id, REQ_DT
FROM PS_REQ_HDR
WHERE
CASE
WHEN CAST(MONTH(GETDATE()) AS INT) > 7
THEN
(REQ_DT
BETWEEN
CAST((SUBSTRING(CAST( YEAR(GETDATE()) AS CHAR),1,4) + '-01-01') AS DATETIME)
AND
CAST((SUBSTRING(CAST( YEAR(GETDATE()) + 1 AS CHAR),1,4) + '-06-30') AS DATETIME)
)
ELSE
(REQ_DT
BETWEEN
CAST((SUBSTRING(CAST( YEAR(GETDATE()) - 1 AS CHAR),1,4) + '-01-01') AS DATETIME)
AND
CAST((SUBSTRING(CAST( YEAR(GETDATE()) AS CHAR),1,4) + '-06-30') AS DATETIME)
)
END

error: Incorrect syntax near the keyword 'BETWEEN'."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-09 : 08:28:59

I think this does it:


select red_id, REQ_DT 
FROM PS_REQ_HDR
WHERE 
CASE WHEN CAST(MONTH(GETDATE()) AS INT) > 7 THEN 
(REQ_DT 
BETWEEN 
CAST((SUBSTRING(CAST( YEAR(GETDATE() + (CASE WHEN CAST(MONTH(GETDATE()) AS INT) > 7 THEN 0 else -1 end)) AS CHAR),1,4) + '-01-01') AS DATETIME)
AND 
CAST((SUBSTRING(CAST( YEAR(GETDATE()) + (CASE WHEN CAST(MONTH(GETDATE()) AS INT) > 7 THEN 1 else 0 end) AS CHAR),1,4) + '-06-30') AS DATETIME)
)

Corey

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 08:54:05

Try this:

SELECT
	red_id,
	REQ_DT 
FROM
	PS_REQ_HDR
	JOIN( 	SELECT
		DATEADD( YEAR, CASE WHEN MONTH(GETDATE()) > 7 THEN 1 ELSE 0 END, '20040101' ) AS FromDate,
		DATEADD( YEAR, CASE WHEN MONTH(GETDATE()) > 7 THEN 1 ELSE 0 END, '20040630' ) AS ToDate ) AS intrvl
	ON REQ_DT BETWEEN intrvl.FromDate AND intrvl.ToDate

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources