How to code for Nulls in Convert statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to code for Nulls in Convert statement?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-16 : 11:33:30

I want to make a CASE WHEN to this statement:

CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' ' + SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7) ADMIN_TIME

because I think that AM.ADMIN_Time is null in some cases and this is causing the app to fail.

When I add this ,CASE WHEN AM.ADMIN_TIME is not null THEN
with a ELSE '' at the end,
it does not compile.

HOw in the above Convert statement can I provide for nulls and not process them?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-16 : 11:44:45

COALESCE(SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7),0)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-16 : 11:46:59

It might help if you post DDL, DML and expected outout per this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

But, on top of that, what do you mean by "not process them?" Do you mean ommit the rows with NULL AM.ADMIN_TIME from your result set or setting the result of the CASE expression to an empty sting? If the former, then i'd suggest using a WHERE clasue: WHERE AM.ADMIN_TIME IS NOT NULL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 11:48:17

just use

COALESCE(CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' ','') + COALESCE(SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7),'') ADMIN_TIME

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 11:49:08

also make sure you specify a length whenever you cast to varchar else the below issue can occur

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-16 : 14:01:54

Yes I would only omit the date/time data if its not there or if it's bad (error) data. WHere do i place this WHERE CLAUSE?

quote:
Originally posted by Lamprey

It might help if you post DDL, DML and expected outout per this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

But, on top of that, what do you mean by "not process them?" Do you mean ommit the rows with NULL AM.ADMIN_TIME from your result set or setting the result of the CASE expression to an empty sting? If the former, then i'd suggest using a WHERE clasue: WHERE AM.ADMIN_TIME IS NOT NULL.

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-16 : 14:02:37

you mean give a legnth for ADMIN TIME?

quote:
Originally posted by visakh16

also make sure you specify a length whenever you cast to varchar else the below issue can occur

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 14:10:33

quote:
Originally posted by AdamWest

you mean give a legnth for ADMIN TIME?

quote:
Originally posted by visakh16

also make sure you specify a length whenever you cast to varchar else the below issue can occur

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

not only that
everywhere you do cast to varchar,numeric etc specify length,precision etc
go through link for reasons

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-16 : 14:34:53

quote:
Originally posted by visakh16

everywhere you do cast to varchar,numeric etc specify length,precision etc

Absolutely! Worries me a lot when I see folk relying on the "default-length" for "varchar"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-16 : 14:58:07

quote:
Originally posted by AdamWest

Yes I would only omit the date/time data if its not there or if it's bad (error) data. WHere do i place this WHERE CLAUSE?

quote:
Originally posted by Lamprey

It might help if you post DDL, DML and expected outout per this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

But, on top of that, what do you mean by "not process them?" Do you mean ommit the rows with NULL AM.ADMIN_TIME from your result set or setting the result of the CASE expression to an empty sting? If the former, then i'd suggest using a WHERE clasue: WHERE AM.ADMIN_TIME IS NOT NULL.

If you don't know where to put a where clause, then I suggest you get a book or read some tutorials on SQL.

I can say that it all starts with a SELECT statement. Here is a link to get you started:
http://msdn.microsoft.com/en-us/library/ms189499.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources