Tsql Case Stmt in a where Condition - Error in the dates

Remo522 21 Reputation points
2022-02-09T19:34:16.463+00:00

I have a where condition that is showing error on the Case statement

where
t.TransactionDate= CASE when Day(getdate()) =1
Then t.TransactionDate >=dateadd(month, datediff(month, 0, getdate())-1, 0) and t.TransactionDate<dateadd(month, datediff(month, 0, getdate()), 0)
else
t.TransactionDate >=dateadd(month, datediff(month, 0, getdate())-1, 0) and t.TransactionDate <= getdate()-1
END

I'm not sure if we can use between for the transactiondate in CASE stmt.Please help.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-02-10T02:42:11.913+00:00

    Hi @Remo522
    The CASE expression has two formats: 

    --Simple CASE expression:     
CASE input_expression     
     WHEN when_expression THEN result_expression [ ...n ]     
     [ ELSE else_result_expression ]     
END     
--Searched CASE expression:    
CASE    
     WHEN Boolean_expression THEN result_expression [ ...n ]     
     [ ELSE else_result_expression ]     
END

    For more details about CASE WHEN, please refer to this document:CASE (Transact-SQL)

    The error in your code is : Boolean expression needs to go in your WHEN clause, not in your THEN (which returns the value)
    In this issue,if you want 

     CASE WHEN GETDATE() = 'first of this month'   
      THEN 'TransactionDate' between 'first day of last month' and 'last day of last month'  
      ELSE 'TransactionDate' between 'first day of last month' and 'current date'   
      END

    Then try this : 

    WHERE t.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)  
  AND t.TransactionDate < CASE WHEN DATEPART(DAY, GETDATE()) = 1   
                               THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)   
                               ELSE GETDATE() END

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.9K Reputation points MVP
    2022-02-09T22:43:35.997+00:00

    showing error on the Case statement

    Next time, please include the error message in your post. The error message may mean nothing to you, but it may help people who are trying to help you.

    In this case, I can see the syntax error. Your question title also tells me where you have gone wrong. There is no CASE statement in SQL. There is a CASE expression. And as all expressions, it returns a value of one of the data types in SQL Server.

    That is, THEN and ELSE must be followed by something that evaluates to a single scalar value, but in your fragment above they are followed by a boolean condition. Since you are comparing a date column, the CASE expression should presumably return a date value.

    Or the entire WHERE clause should be expressed in some other way. Since I don't want to make a guess of what you are trying to do, I abstain from making suggestions. Maybe you could clarify what you are trying to achieve?

  2. Olaf Helper 41,006 Reputation points
    2022-02-10T06:02:52.283+00:00

    else t.TransactionDate >=dateadd(month, datediff(month, 0, getdate())-1, 0) and t.TransactionDate <= getdate()-1

    What for a scalar result do you expect here for the ELSE part?

    0 comments
  3. Erland Sommarskog 101.9K Reputation points MVP
    2022-02-10T22:08:55.26+00:00

    Assuming that your query has to work with data of some size, I think it may be better to write the query this way: 

    ; WITH CTE AS (
    --- the rest of your query here-
)
SELECT *
FROM  CTE
WHERE  @dayofmonth = 1
   AND  transactionDate BETWEEN convert(date, dateadd(MONTH, -1, sysdatetime())) AND convert(date, dateadd(DAY, -1, sysdatetime()))
UNION ALL 
SELECT *
FROM  CTE
WHERE  @dayofmonth > 1
   AND  transactionDate BETWEEN convert(date, dateadd(DAY-1, sysdatetime())) AND convert(date, sysdatetime())

    This is more long-winded than solutions with OR, but I think chances are better for a plan that utiliises an index on TransactionDate. The variables in the WHERE clauses of the UNION ALL operation will ensure that only one of the queries are actually executed.

    Note: I'm assuming that the data type of the TransactionDate column is date.

    0 comments