Sql Isnull()

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
niceguy971Asked:
Who is Participating?
 
JustAndreiConnect With a Mentor Commented:
Robert_schutt's solution is correct, however, if you want even shorter solution, consider this:
ISNULL(CONVERT(varchar, BuyDate, 110), ' ') AS BuyDate
0
 
Robert SchuttConnect With a Mentor Software EngineerCommented:
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
 
Pratima PharandeConnect With a Mentor 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
0
 
igordevelopConnect With a Mentor 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
0
 
niceguy971Author Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.