Sql Isnull()

niceguy971
niceguy971 used Ask the Experts™
on
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Robert SchuttSoftware Engineer
Commented:
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

Pratima Pharande
Commented:
try this also

CASE
  WHEN (CONVERT(varchar(10),(ISNULL(BuyDate, '')), 110)='01-01-1900') THEN (' ')
   ELSE (CONVERT(varchar(10),BuyDate, 110))
END as BuyDate
igordevelop
Commented:
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
Commented:
Robert_schutt's solution is correct, however, if you want even shorter solution, consider this:
ISNULL(CONVERT(varchar, BuyDate, 110), ' ') AS BuyDate
niceguy971

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial