Substitute NULL value on converted date field?

Question

Substitute NULL value on converted date field?

2Tall

SSChampion

Points: 14644
More actions
April 17, 2008 at 1:53 am

#186564

Hi Guys. I have a select query as follows (sample code):
SELECT SalesOrderId, convert(varchar, Date, 103)AS ExpiryDate, Type
FROM dbo.Values INNER JOIN
SalesOrders ON SalesOrders.SalesOrder = dbo.Values.RecordNumber INNER JOIN
dbo.DataNameFields ON dbo.DataNameFields.DataField = dbo.Values.DataField
WHERE dbo.Values.DataField = '43' AND dbo.SalesOrders.Type = 'Q'
The field that contains the 'Date' has a number of Null Values. Rather than the query returning 'Null' I thought it might be an idea to return 'No Date Specified' as I will be using the query in a report (or even exclude NULL from query results?).
Is that possible or even viable?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 15 posts - 1 through 15 (of 17 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Lookup the ISNULL function. Should do what you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nokiathebest SSC Rookie Points: 32 More actions · Answer 2

OR you can use:

----------------

SELECT

CASE

WHEN [DateField] IS NULL then 'No Date Specified'

ELSE convert(varchar, Date, 103)AS ExpiryDate

END AS DD

FROM

[AppsGo].[dbo].[Sales]

2Tall SSChampion Points: 14644 More actions · Answer 3

Thanks for the quick response. I looked up the ISNULL prior to posting but could not get it to work!

Tried:

SELECT SalesOrderId, convert (varchar, (ISNULL(CustomFieldValueDate,'No Date',103))AS ExpiryDate, Type

Have tried code below, returns error 'Incorrect syntax near the keyword 'CASE'

SELECT SalesOrderId, Type

CASE

WHEN [Date] IS NULL then 'No Date Specified'

ELSE convert(varchar, Date, 103)AS ExpiryDate

END AS DD

FROM dbo.Values INNER JOIN

SalesOrders ON SalesOrders.SalesOrder = dbo.Values.RecordNumber INNER JOIN

dbo.DataNameFields ON dbo.DataNameFields.DataField = dbo.Values.DataField

WHERE dbo.Values.DataField = '43' AND dbo.SalesOrders.Type = 'Q'

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Ad a comma after Type and remove the AS clause in the CASE part.

SELECTSalesOrderId,

Type,

CASE

WHEN [Date] IS NULL then 'No Date Specified'

ELSE CONVERT(VARCHAR, Date, 103)

END AS ExpiryDate

FROMdbo.Values

INNER JOINSalesOrders ON SalesOrders.SalesOrder = dbo.Values.RecordNumber

INNER JOINdbo.DataNameFields ON dbo.DataNameFields.DataField = dbo.Values.DataField

WHEREdbo.Values.DataField = '43'

AND dbo.SalesOrders.Type = 'Q'

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

SELECTSalesOrderId,

Type,

ISNULL(CONVERT(VARCHAR, Date, 103), 'No Date Specified') AS ExpiryDate

FROMdbo.Values

INNER JOINSalesOrders ON SalesOrders.SalesOrder = dbo.Values.RecordNumber

INNER JOINdbo.DataNameFields ON dbo.DataNameFields.DataField = dbo.Values.DataField

WHEREdbo.Values.DataField = '43'

AND dbo.SalesOrders.Type = 'Q'

N 56°04'39.16"
E 12°55'05.25"

nokiathebest SSC Rookie Points: 32 More actions · Answer 6

Peso (4/17/2008)
SELECTSalesOrderId, Type, ISNULL(CONVERT(VARCHAR, Date, 103), 'No Date Specified') AS ExpiryDate FROMdbo.Values INNER JOINSalesOrders ON SalesOrders.SalesOrder = dbo.Values.RecordNumber INNER JOINdbo.DataNameFields ON dbo.DataNameFields.DataField = dbo.Values.DataField WHEREdbo.Values.DataField = '43' AND dbo.SalesOrders.Type = 'Q'

Thks peso for the idea peso, faster to write and maybe better for performance ! ! !

2Tall SSChampion Points: 14644 More actions · Answer 7

Peso awesome, many thanks to you and to all those that gave their time in assisting me with this problem.

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

john.arnott SSChampion Points: 11882 More actions · Answer 8

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/

2Tall SSChampion Points: 14644 More actions · Answer 9

Thanks for your input John.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 10

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 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)

James Horsley Hall of Fame Points: 3196 More actions · Answer 11

ISNULL(CONVERT(VARCHAR, Date, 103), 'No Date Specified') AS ExpiryDate

Just beware of a little potential gotcha here - using convert with varchar and no specified length default to a varchar(20) return - so above converts the date to varchar(20) with alternative text which luckily is <20 chars. If you used a longer string than 'No Date Specified' it would be truncated to 20 chars long in above code - in general I like to specify length on converts (even if the length I want is 20) as this protects against unexpected defaults ...

James Horsley
Workflow Consulting Limited

john.arnott SSChampion Points: 11882 More actions · Answer 12

Please tell me that you don't believe in the myth of code portability...
--Jeff Moden

It's not so much code portability as developer portability. I prefer being able to use as similar approach as possible in our T-SQL and DB2 code and remember as few differences as necessiary. Since "COALESCE" does the same job as "ISNULL" as well as add flexibility, I'd just as soon learn to use this weirdly named function on both platforms, even if it sounds like gelatin setting.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

john.arnott (4/21/2008)
Please tell me that you don't believe in the myth of code portability...
--Jeff Moden
It's not so much code portability as developer portability. I prefer being able to use as similar approach as possible in our T-SQL and DB2 code and remember as few differences as necessiary. Since "COALESCE" does the same job as "ISNULL" as well as add flexibility, I'd just as soon learn to use this weirdly named function on both platforms, even if it sounds like gelatin setting.

I remember developer portability. About twelve years ago, someone walked into my cube at the time, handed me a SQL Server book and told me to stop building Access databases...Poof - I'd been "ported"....:D

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

DAVNovak Hall of Fame Points: 3120 More actions · Answer 14

Matt,

At least they handed you a SQL Server book to start with. All they gave me was the instructions: 'Here is a SQL Server database. Make it work.' Before that, All I was doing was MS Access 2.0 and 97. Those were such fun days.

Dave Novak