CASE STATEMENT

Question

CASE STATEMENT

Jeremy...

Ten Centuries

Points: 1299
More actions
April 16, 2012 at 10:08 am

#254830

I'm having an issue with this case statement:
CASE WHEN CLPCR.LicenseExpirationDate IS NOT NULL THEN CAST(CLPCR.LicenseExpirationDate AS DATE)
WHEN CLPCR.LicenseExpirationDate IS NULL THEN 'Perpetual' END AS 'Expiration'
When executing, i get this error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
I understand that it is looking for a date not a word, but am not sure how to make it work. Please let me know your thoughts. thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

You are trying to cast some values as date and others as character data in the same result column. You can't do that. I don't know what working means. What do you want it do do? Please provide some DDL and sample data and results. Look at Jeff Moden's article in my signature for the proper way to post.

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jeremy... (4/16/2012)
I'm having an issue with this case statement:
CASE WHEN CLPCR.LicenseExpirationDate IS NOT NULL THEN CAST(CLPCR.LicenseExpirationDate AS DATE)
WHEN CLPCR.LicenseExpirationDate IS NULL THEN 'Perpetual' END AS 'Expiration'
When executing, i get this error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
I understand that it is looking for a date not a word, but am not sure how to make it work. Please let me know your thoughts. thanks.

You need to cast CLPCR.LicenseExpirationDate to a character string, not a date since you want this column to contain a string value if the value of this column is null.

Jared SSC Guru Points: 61793 More actions · Answer 3

If you want to keep these results as dates, try replacing 'Perpetual' with '1900-01-01' OR '9999-12-31'

Jared
CE - Microsoft

Jeremy... Ten Centuries Points: 1299 More actions · Answer 4

Thanks for the help so far. I am essentially trying to show the expiration date for an item, however, when the value is null, this means the license has no expiration, and therefore is a "perpetual license". So the data would look something like this (with more variation):

2012-07-31

Perpetual

2012-07-31

Perpetual

I am not locked in on how I display the date, so if the date can be converted to something else, that may work to. thanks.

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 5

you can also try an else instead of another "When" clause, with a proper cast

Jared SSC Guru Points: 61793 More actions · Answer 6

Jeremy... (4/16/2012)
Thanks for the help so far. I am essentially trying to show the expiration date for an item, however, when the value is null, this means the license has no expiration, and therefore is a "perpetual license". So the data would look something like this (with more variation):
2012-07-31
2012-07-31
2012-07-31
Perpetual
2012-07-31
2012-07-31
Perpetual
I am not locked in on how I display the date, so if the date can be converted to something else, that may work to. thanks.

Is this for a report? If so, then do this:

CASE WHEN CLPCR.LicenseExpirationDate IS NOT NULL THEN CAST(CLPCR.LicenseExpirationDate AS VARCHAR(10))

ELSE 'Perpetual' END AS 'Expiration'

If this is for an application, don't change it to VARCHAR(10)... Make an identifier such as 1900-01-01 or 1753-01-01 or 9999-12-31 or 2079-06-06 (depending on your datatype).

EDIT: If doing this in reporting services, don't even use a case. Build your expression in the report builder.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 7

sqlbi.vvamsi (4/16/2012)
you can also try an else instead of another "When" clause, with a proper cast

And still get an error...

Jared
CE - Microsoft

Jeremy... Ten Centuries Points: 1299 More actions · Answer 8

SQLKnowItAll (4/16/2012)
Jeremy... (4/16/2012)
Thanks for the help so far. I am essentially trying to show the expiration date for an item, however, when the value is null, this means the license has no expiration, and therefore is a "perpetual license". So the data would look something like this (with more variation):
2012-07-31
2012-07-31
2012-07-31
Perpetual
2012-07-31
2012-07-31
Perpetual
I am not locked in on how I display the date, so if the date can be converted to something else, that may work to. thanks.
Is this for a report? If so, then do this:
CASE WHEN CLPCR.LicenseExpirationDate IS NOT NULL THEN CAST(CLPCR.LicenseExpirationDate AS VARCHAR(10))
ELSE 'Perpetual' END AS 'Expiration'
If this is for an application, don't change it to VARCHAR(10)... Make an identifier such as 1900-01-01 or 1753-01-01 or 9999-12-31 or 2079-06-06 (depending on your datatype).
EDIT: If doing this in reporting services, don't even use a case. Build your expression in the report builder.

That did it. Thank you!