Solved

CASE STATEMENT WHEN DATE IS NULL

Posted on 2014-04-02
6
1,869 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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 41

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 43
Index and Stats Management-Specific tables 8 39
Many to one in one row 2 40
Estimating my database size 7 14
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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