SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Handling NULL


Handling NULL

Author
Message
kapil_kk
kapil_kk
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9327 Visits: 2778
Comments posted to this topic are about the item Handling NULL

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Danny Ocean
Danny Ocean
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3110 Visits: 1549
Good question kapil.
I feel that this question contains it's answer in itself. Why i think so? Let me tell you.

In question, you told that you need the output like:- "Apr 9 2013 11:38AM".
This output comes when we convert any datetime in varchar (string format) with different format option. like:-
SELECT CONVERT(VARCHAR, GETDATE(),100), CONVERT(VARCHAR, GETDATE(),101), CONVERT(VARCHAR, GETDATE(),110)



This one point to correct answer. :-)


One minor mistake i found that, You need output like "Apr 9 2013 11:38AM", But below all queries give today day. oops !!! :-)
No problem.

Thanks of start my day with good question.

:-)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
kapil_kk
kapil_kk
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9327 Visits: 2778

One minor mistake i found that, You need output like "Apr 9 2013 11:38AM", But below all queries give today day. oops !!! :-)

Hi Vinay,
Actually, I run this scenario on 9 April and put it here for QOTD. I was not aware when it will going to be published....
Hmmm I missed the word 'like' :-P it should be written as 'If I want output like this:' Hehe
Thanks for prompting me :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31114 Visits: 7621
Nice, simple back-to-basics question, thanks

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Raghavendra Mudugal
Raghavendra Mudugal
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5042 Visits: 2958
Nice one, thank you for posting.

(By seeing the output table.. it was sure that date is today's and from the options... I selected 4 quickly and then... realized the format of the date in the output is not same as getdate()'s, so I confirmed "okay it needs convert..." and option was 3... okay... finally I got the answer right; but "damn!! it showed the answer as wrong and was wondering... and again realized.. I did not switch the answer to 3... " Note to self: mental analysis is good, but online submission needs accurate practical selection.)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7703 Visits: 3889
Be careful with omitting information in your SQL.
For example, it is good practice to explicitly specify the nullability of your columns.

Had ANSI_NULL_DFLT_OFF for the session, the insert would fail.

Msg 515, Level 16, State 2, Line 9
Cannot insert the value NULL into column 'IsAvailable', table 'tempdb.dbo.#Test_______________________________________________________________________________________________________________000000000044'; column does not allow nulls. INSERT fails.

SET ANSI_NULL_DFLT_OFF (Transact-SQL)


Also, in the CONVERTS, you should specify the size to avoid issues and misunderstandings.
convert(varchar(30), colxyz)


instead of
convert(varchar, colxyz)



Best Regards,

Chris Büttner
Raghavendra Mudugal
Raghavendra Mudugal
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5042 Visits: 2958
Christian Buettner-167247 (4/22/2013)
Be careful with omitting information in your SQL.
For example, it is good practice to explicitly specify the nullability of your columns.

Had ANSI_NULL_DFLT_OFF for the session, the insert would fail.

Msg 515, Level 16, State 2, Line 9
Cannot insert the value NULL into column 'IsAvailable', table 'tempdb.dbo.#Test_______________________________________________________________________________________________________________000000000044'; column does not allow nulls. INSERT fails.

SET ANSI_NULL_DFLT_OFF (Transact-SQL)


Also, in the CONVERTS, you should specify the size to avoid issues and misunderstandings.
convert(varchar(30), colxyz)


instead of
convert(varchar, colxyz)



Agreed;

But if you use session property and check the default value for ANSI_NULLS is always 1. (I guess in production servers this may be the case for some specific SPs or any SQL batch where such explicit usage is needed, but in general it is not necessary, we can just consider the SQL defaults and work with that.)

and data size mention is good but on the GetDate value it does not effects even if you mention 100 (apart from that, regular varchar/char columns, it is good practice)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Thomas Abraham
Thomas Abraham
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5487 Visits: 2256
Thank you for you contribution. More please.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Dineshbabu
Dineshbabu
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1782 Visits: 569
Kapil good going...

Good chance to recall return types & Data type precedence of ISNULL,CONVERT & CASE...

--
Dineshbabu
Desire to learn new things..
sujay 24116
sujay 24116
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
If I am not wrong... This may be easier to understand.

----------------------------------------------------------------------------------------
SELECT ID, ISNULL(CAST(IsAvailable AS VARCHAR), GETDATE()) AS IsAvailable FROM #Test
-----------------------------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search