Solved

Sql Isnull()

Posted on 2012-12-23

4 solutions

430 Views

Last Modified: 2013-02-13

I have the following Select statement:

select CustomerID, Name,

CASE
WHEN (CONVERT(varchar(10),(ISNULL(BuyDate, '')), 110)='01-01-1900') THEN (' ')
ELSE (CONVERT(varchar(10),(ISNULL(BuyDate, '')), 110))
END as BuyDate

from Customers;

BuyDate --it's datetime field in the Customers table

ISNULLL(BuyDate, '') returns 01-01-1900 if BuyDate contains NULL.

Is there BETTER WAY to re-write the below piece of my sql sttement ???

CASE
WHEN (CONVERT(varchar(10),(ISNULL(BuyDate, '')), 110)='01-01-1900') THEN (' ')
ELSE (CONVERT(varchar(10),(ISNULL(BuyDate, '')), 110))
END as BuyDate

Thanks

Comment

Question by:niceguy971

[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

Join The Community

5 Comments

LVL 35

Assisted Solution

by:Robert Schutt

Robert Schutt earned 125 total points

ID: 387176782012-12-23

not sure if it's much better but at least a bit shorter:

CASE
  WHEN BuyDate IS NULL THEN (' ')
   ELSE (CONVERT(varchar(10), BuyDate, 110))
END as BuyDate

Open in new window

LVL 39

Assisted Solution

by:Pratima Pharande

Pratima Pharande earned 125 total points

ID: 387178922012-12-23

try this also

CASE
WHEN (CONVERT(varchar(10),(ISNULL(BuyDate, '')), 110)='01-01-1900') THEN (' ')
ELSE (CONVERT(varchar(10),BuyDate, 110))
END as BuyDate

LVL 1

Assisted Solution

by:igordevelop

igordevelop earned 125 total points

ID: 387180282012-12-24

Hi,

This seems much simpler, in case you don't need the null date in string format:

select EmployeeID, FirstName, ISNULL(BuyDate, NULL)
from Customers;

Let me know if anything.

Regards,
Igor

LVL 8

Accepted Solution

by:JustAndrei

JustAndrei earned 125 total points

ID: 387203682012-12-25

Robert_schutt's solution is correct, however, if you want even shorter solution, consider this:
ISNULL(CONVERT(varchar, BuyDate, 110), ' ') AS BuyDate

Author Closing Comment

by:niceguy971

ID: 388867732013-02-13

Thanks

Featured Post

Question has a verified solution.

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

.NET Number to Text Converter

Article by: käµfm³d 👽

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…

.NET Programming

Share One TLS Certificate for Remote Desktop Services and SQL Server

Article by: Brian

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.

How to access multiple mailboxes from one account in MS Exchange Server 2010 - video tutorial

Video by: Adam

There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

Learn how to improve Mouse, Pointer and Cursor visibility in Windows 10

Video by: Andrew

In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…