Solved

CASE STATEMENT WHEN DATE IS NULL

Posted on 2014-04-02
6
1,751 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now