CODEWITHSUNDEEP
sqlt-sqlif-statementcase
michaalis
michaalis

Reputation: 131

If/then/else in SQL Query

I would like to check a date value in my SQL query. If a date is equal to a predefined date then do not print anything, ELSE print the existing date value.

How can I write it correctly in order to take the desired date value ?

I have the following query:

(SELECT (CASE 
             WHEN (PaymentsMade.PaymentDate = '09/09/1987') THEN ' '
             ELSE PaymentsMade.PaymentDate
         END)
 ) as dateOfPayment

When I run this query it works correctly when the date is equal to '09/09/1987' , whereas when the date is not equal to '09/09/1987' it prints '01/01/1900'.

How can I retrieve the dates values that are not equal to the predefined date '09/09/1987'?

Any advice would be appreciated. Thanks

Upvotes: 1

Views: 3581

Answers (4)

user359040
user359040

Reputation:

The CASE clause needs to return a consistently-typed value, so it is implicitly converting a space to a date (which is evaluated as 1 Jan 1900).

You have two choices:

  • select a null instead of a blank space.
  • explicitly cast the date in the else condition to a string.

Here's an (implicit) example of the former:

SELECT (CASE WHEN PaymentsMade.PaymentDate <> '09/09/1987' 
             THEN PaymentsMade.PaymentDate
         END)
       as dateOfPayment

Upvotes: 2

Andriy M
Andriy M

Reputation: 77737

You could use NULLIF to replace a specific date with a NULL:

SELECT NULLIF(PaymentsMade.PaymentDate, '09/09/1987')
FROM ...

Don't just use an empty string, because it would be converted to the type of PaymentDate, which is probably a datetime, and an equivalent datetime for '' would be 1900-01-01 00:00:00.000.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86808

Using a CASE statement changes the value in that field, but doesn't change which rows are returned.

You appear to want to filter out rows, and if that is the case, use a WHERE clause...

SELECT
  *
FROM
  PaymentsMade
WHERE
  PaymentDate <> '09/09/1987'

Upvotes: 0

gbn
gbn

Reputation: 432712

Use NULL, not empty string

An empty string is cast to zero implicitly, which is '01/01/1900'

SELECT CAST('' AS datetime)

Upvotes: 0

Related Questions