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

niceguy971

Asked: 2012-12-23

Advertise Here

4 Solutions

LVL 35

Robert SchuttSoftware EngineerCommented: 2012-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

Pratima PharandeCommented: 2012-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

igordevelopCommented: 2012-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

JustAndreiCommented: 2012-12-25

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

niceguy971Author Commented: 2013-02-13

Thanks

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

Sql Isnull()

Join & Write a Comment Already a member? Login.

Featured Post