Solved

Sql Isnull()

Posted on 2012-12-23
5
422 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
0
Comment
Question by:niceguy971
5 Comments
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 125 total points
ID: 38717678
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

0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 125 total points
ID: 38717892
try this also

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

Assisted Solution

by:igordevelop
igordevelop earned 125 total points
ID: 38718028
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
0
 
LVL 8

Accepted Solution

by:
JustAndrei earned 125 total points
ID: 38720368
Robert_schutt's solution is correct, however, if you want even shorter solution, consider this:
ISNULL(CONVERT(varchar, BuyDate, 110), ' ') AS BuyDate
0
 

Author Closing Comment

by:niceguy971
ID: 38886773
Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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 …
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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

11 Experts available now in Live!

Get 1:1 Help Now