CASE function

29 Jun 2006 5:41 PM

Rob

Hi,

I have a date column where the application users puposely enter a date way
in the future as part of their business rule. For instance, entering the year
2033 if the given value for this date column is unknown.

I need to programmatically retrieve this date and represent those
out-of-whack dates to show as the current date + 10 days.

I created the following SELECT stmt. using the CASE function:

SELECT .....
CASE post_datetime
WHEN post_datetime > getdate()+365 THEN getdate()+10
....

However, SQL QA returns an error (Incorrect syntax near '>') when I try to
execute this stmt. What am I doing wrong here. Please help. Thanks.

Regards,

- Rob.

29 Jun 2006 5:48 PM

Tracy McKibben

Rob wrote:
Show quote

> Hi,
>
> I have a date column where the application users puposely enter a date way
> in the future as part of their business rule. For instance, entering the year
> 2033 if the given value for this date column is unknown.
>
> I need to programmatically retrieve this date and represent those
> out-of-whack dates to show as the current date + 10 days.
>
> I created the following SELECT stmt. using the CASE function:
>
> SELECT .....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
> ...
>
> However, SQL QA returns an error (Incorrect syntax near '>') when I try to
> execute this stmt. What am I doing wrong here. Please help. Thanks.
>
> Regards,
>
> - Rob.

Try this instead:

CASE WHEN post_datetime > GETDATE() + 365 THEN GETDATE() + 10 ELSE
post_datetime END

29 Jun 2006 5:52 PM

Aaron Bertrand [SQL Server MVP]

> SELECT .....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10

There are two general forms of the CASE expression (it is not a function).

You can either say

CASE [expression] WHEN [value] THEN [value] END

or

CASE WHEN [expression][operator][value] THEN [value] END

You combined the two in a way I don't recall ever seeing (and as you have
found out, the syntax is invalid). You need the latter, because you are
testing a more complex expression than simple equality.

Try:

SELECT
CASE WHEN post_datetime > getdate()+365 THEN getdate()+10 END