SQL Server case in where statement

Question

I am working with a query which contains a CASE statement in the WHERE clause. But SQL Server 2008 is giving some errors while executing it.

Also I want to get the FIRST day of previous month.

Can anyone please help me with the correct query?

Here is case part of the query:

WHERE  
    FISCALCALENDARYEAR.STARTDATE = 
        CASE FORMAT(FISCALCALENDARYEAR.STARTDATE,'MM') BETWEEN 01 AND 10 
           THEN DATEADD(m, -1, DATEADD(mm, DATEDIFF(m, 0, FISCALCALENDARYEAR.STARTDATE), 0))
           ELSE FISCALCALENDARYEAR.STARTDATE BETWEEN '2015/01/01' AND '2015/12/31'
        END

The error :

Incorrect syntax near the keyword 'between'. or Incorrect syntax near the ' >=' And error in then of case Incorrect syntax

Update : Now i have error in the else block :

WHERE  FISCALCALENDARYEAR.STARTDATE = CASE WHEN FORMAT(FISCALCALENDARYEAR.STARTDATE,'MM') between 01 AND 10 
            THEN DATEADD( m,-1,DATEADD(mm, DATEDIFF(m,0,FISCALCALENDARYEAR.STARTDATE), 0))  --get first day on the last month
            ELSE 
                DIMENSIONFOCUSBALANCE.ACCOUNTINGDATE  == FORMAT(GETDATE() ,'dd/MM/yyyy')    
                AND FISCALCALENDARYEAR.STARTDATE      == FORMAT(GETDATE() ,'dd/MM/yyyy')
END

Structure better your query, so you can see, where is a problem. You are for example missing the keyword "WHEN" after "CASE", so there's a syntax error :-) — krtek, Nov 30 '15 at 11:44
`CASE` in T-SQL is **NOT** a statement - it's an **expression** that can return an **atmoic value** (like 1, 2, 42, 4711) - but it **cannot** return a "code block" to be evaluated or executed, nor can it control the flow of your app. You need to **rewrite** your `WHERE` to use the `CASE` the way it's designed to be used — marc_s, Nov 30 '15 at 11:48
please show your full sql query to understand more and table structure. — Ajay2707, Nov 30 '15 at 12:00
Thank you for reply , i was missing "WHEN" after "CASE". i updated my poste — stoner, Nov 30 '15 at 12:40

score 1 · Answer 1 · edited May 23 '17 at 12:00

1

Syntax for CASE with BETWEEN

SELECT CASE
          WHEN Expression BETWEEN 0 AND 10 THEN '0-10'
          WHEN Expression BETWEEN 10 AND 20 THEN '10-20'
          WHEN Expression BETWEEN 20 AND 30 THEN '20-30'
          ELSE 'NOTHING'
       END AS 'ColumnName'

First day of previous month Reference

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) 

SELECT DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))

edited May 23 '17 at 12:00

Community

1
1

answered Nov 30 '15 at 11:40

wiretext

3,302
14
19

Can you please review my update qiestion , i have problem in else block – stoner Nov 30 '15 at 14:11

score 0 · Answer 2 · answered Nov 30 '15 at 11:58

Your case statement is missing the WHEN argument (see Books Online for more). It should go between CASE and FORMAT.

Did you know there are a two different types of case statement. Here is a simple example of both:

-- Type 1.
CASE FieldName
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    ELSE 'c'
END

-- Type 2.
CASE 
    WHEN FieldName = 1 THEN 'a'
    WHEN FieldName = 2 THEN 'b'
    ELSE 'c'
END

Type 1 is great for mapping 1 value to another. You can only check for exact matches (no BETWEENs, < or > here). Type 2 generally requires more typing, but it is much more powerful.

score 0 · Answer 3 · answered Oct 27 '16 at 16:17

WHERE  FISCALCALENDARYEAR.STARTDATE = CASE WHEN FORMAT(FISCALCALENDARYEAR.STARTDATE,'MM') between 01 AND 10 
                THEN DATEADD( m,-1,DATEADD(mm, DATEDIFF(m,0,FISCALCALENDARYEAR.STARTDATE), 0))  --get first day on the last month
                ELSE 
                    DIMENSIONFOCUSBALANCE.ACCOUNTINGDATE  == FORMAT(GETDATE() ,'dd/MM/yyyy')    
                    AND FISCALCALENDARYEAR.STARTDATE      == FORMAT(GETDATE() ,'dd/MM/yyyy')
    END

First off, the CASE statement must be part of the expression, not the expression itself.

WHERE  FISCALCALENDARYEAR.STARTDATE = (CASE WHEN FORMAT(FISCALCALENDARYEAR.STARTDATE,'MM') between 01 AND 10 
                THEN DATEADD( m,-1,DATEADD(mm, DATEDIFF(m,0,FISCALCALENDARYEAR.STARTDATE), 0)) 
                ELSE 
                    DIMENSIONFOCUSBALANCE.ACCOUNTINGDATE  == FORMAT(GETDATE() ,'dd/MM/yyyy') END)    
                    AND FISCALCALENDARYEAR.STARTDATE      == FORMAT(GETDATE() ,'dd/MM/yyyy')

SQL Server case in where statement

3 Answers3