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
Solved

CASE STATEMENT WHEN DATE IS NULL

Posted on 2014-04-02
6
1,833 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: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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

792 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