Substitute NULL value on converted date field?

Question

Substitute NULL value on converted date field?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Matt: You got a book!?!? I'm so jealous!

I built an Access database (I had to look up the word "relational" in a dictionary to find out what the heck a "relational database" was). The company doing all our IS work up-converted it to SQL Server a few months later, and said, "here's your database, have fun".

Before that, I was in Sales.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 2

Jeff Moden (4/20/2008)
john.arnott (4/18/2008)
It's been suggested that "COALESCE" is preferable to ISNULL.
It allows for a list of arguments, the first of which evaluates to not null being returned.
Coalesce is ANSI standard; ISNULL is Microsoft only, so for portability of code (and sanity of a developer working multiple platforms), COALESCE would be the choice.
see artilcle on nulls:
http://www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/
Please tell me that you don't believe in the myth of code portability...

Jeff, how many times do I have to tell you, code is very, very portable. You copy it to a USB drive, either a hard drive if it's really big code, or a flash drive if it's kind of small code. Then you walk across the room, carrying the drive with you. Bam! Portable code! It's SOOOOOO easy.

But really, since Coalesce is ANSI standard, and IsNull isn't, I do prefer Coalesce. Not by much, but I do prefer it. Since it can take multiple arguments, I also find it more useful in many cases.

My favorite example is piecing names together.

coalesce(fname + ' ' + mname + ' ' + lname, fname + ' ' + lname, fname, title + ' ' + lname, 'Mr/Ms ' + lname, 'Whom it may Concern')

Instead of nested IsNulls.

isnull(isnull(isnull(isnull(isnull(fname + ' ' + mname + ' ' + lname, fname + ' ' + lname), fname), title + ' ' + lname), 'Mr/Ms ' + lname), 'Whom it may Concern')

It's not that different, but I find the first easier to deal with, especially in debugging or refactoring.

And since I prefer to use Coalesce on the complex ones, I stick to it on the simple ones, for consistency and standards.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Heh... I understand that COALESCE is more portable than ISNULL... that's one of the fortunate functions... I just wanted to make sure that someone didn't actually believe they could write fully portable code and have it still be useful and performant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)