Solved

CASE STATEMENT WHEN DATE IS NULL

Posted on 2014-04-02
6
1,798 Views
Last Modified: 2014-04-02
Pretty new to SQL and I’m attempting to write this CASE statement:
,CASE
      WHEN e_sum.ASSURANCE_BUNDLE_TYPE_ID = 5 THEN 'N/A'
      WHEN e_sum.ASSURANCE_BUNDLE_TYPE_ID = 3 AND e_sum.ESTIMATED_START_DATE IS NULL THEN 'Awaiting'
      ELSE e_sum.ESTIMATED_START_DATE
      END AS [Estimated Start Date Case]

But get this error: Conversion failed when converting date and/or time from character string.


How can I correct this?
0
Comment
Question by:DallasTR
6 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39972464
Hi Dallas,

The problem isn't the NULL, per se.  It's that the CASE statement tries to return different data types.

All of the possible returned values must be of the same type.  It looks like you're trying to return a string OR a date.  Modify the structure so that you always return a string or always return a date.


Good Luck,
Kent
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 39972465
\
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 39972487
Try this.
CASE
      WHEN e_sum.ASSURANCE_BUNDLE_TYPE_ID = 5 THEN 'N/A'
      WHEN e_sum.ASSURANCE_BUNDLE_TYPE_ID = 3 AND e_sum.ESTIMATED_START_DATE IS NULL THEN 'Awaiting'
      ELSE CONVERT(varchar(30),e_sum.ESTIMATED_START_DATE)
      END AS [Estimated Start Date Case]

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:DallasTR
ID: 39972488
Understand, but given I am trying to return two different data types it would stand to reason that I'd have to convert the date to a string.  How would this be done for this case statement?
0
 

Author Comment

by:DallasTR
ID: 39972498
Awe see Sharath gave an example...I'll give it a try.
0
 

Author Closing Comment

by:DallasTR
ID: 39972529
This worked perfectly - thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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