CODEWITHSUNDEEP
sqlsql-servert-sql
stokoe0990
stokoe0990

Reputation: 473

CASE expressions on datetime columns

I'm trying to access a datetime column to find out whether the date is within a week from today, or overdue. Then write a new column's value to say Incoming, Overdue or Fine.

SELECT
CASE next_action_date
WHEN (BETWEEN GETDATE()+7 AND GETDATE()) THEN 'Incoming'
WHEN (< GETDATE()) THEN 'Overdue'
ELSE 'Fine'
END AS condition
FROM
tableName

This is what I've got so far, but as you can probably see by looking, it doesn't work at all:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'BETWEEN'.

Upvotes: 0

Views: 202

Answers (5)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727077

There are two syntaxes of the CASE expression - the so-called simple one that compares a single value against a list of other values, and a searched one with generic boolean conditions. You picked the simple case, but it does not have enough flexibility for what you need; you should switch to the searched syntax, like this:

SELECT
CASE
    WHEN next_action_date BETWEEN GETDATE() AND GETDATE()+7 THEN 'Incoming'
    WHEN next_action_date < GETDATE() THEN 'Overdue'
    ELSE 'Fine'
END AS condition
FROM
tableName

Upvotes: 5

Devart
Devart

Reputation: 122042

Try this one -

DECLARE @Date DATETIME
SELECT @Date = GETDATE()

SELECT
    condition = CASE 
        WHEN t.next_action_date BETWEEN @Date AND DATEADD(DAY, 7, @Date) THEN 'Incoming'
        WHEN t.next_action_date < @Date THEN 'Overdue'
        ELSE 'Fine'
    END 
FROM dbo.tableName t

Upvotes: 2

Kevin Kunderman
Kevin Kunderman

Reputation: 2134

You should uses the other form of the case statement

    SELECT
    CASE 
        WHEN (next_action_date BETWEEN GETDATE()+7 AND GETDATE()) THEN 'Incoming'
        WHEN (next_action_date < GETDATE()) THEN 'Overdue'
        ELSE 'Fine'
    END AS condition
    FROM
    tableName

http://www.devx.com/tips/Tip/15633

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try

select CASE 
    when next_action_date between GETDATE() and GETDATE()+7 then 'Incoming'
    when next_action_date < GETDATE() THEN 'Overdue'
    else 'fine' end as Condition
from(
    select GETDATE()+6 next_action_date
)x

Upvotes: 4

Lasse Edsvik
Lasse Edsvik

Reputation: 9298

use DATEADD(Day, 7, GETDATE())

Upvotes: 1

Related Questions