Solved

Sql Isnull()

Posted on 2012-12-23
5
426 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
[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
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why use this lambda? 12 61
How to repeat the data 4 30
find SQL job run average duration 24 54
Database maintenance 36 100
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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