Solved

SELECT statement with CASE and CAST

Posted on 2014-09-18
3
237 Views
Last Modified: 2014-09-22
i am trying to return a string value when a timestamp field value is null.  below is my select statement.  I am getting an error message on the data type.  Can I use the cast() as varchar to resolve?

select
first_name,
last_name,
case when table.event_id = 5 and table.timestamp isnull then 'n/a' else table.timestamp end as ad_timestamp

from XXXXXX
0
Comment
Question by:szadroga
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 40330359
All possible values returned in the CASE construct have to be the same datatype.

Cast the datetime/timestamp to any character (string) type and you'll be fine.


Good Luck!
Kent
0
 

Author Comment

by:szadroga
ID: 40330364
where do I perform the cast() in my syntax?  I cannot get it to work
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 40330370
select
first_name,
last_name,
case when table.event_id = 5 and table.timestamp isnull then 'n/a'
else cast (table.timestamp as varchar (20)) end as ad_timestamp
from XXXXXX
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question