April 20, 2013 at 12:37 pm
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/
April 21, 2013 at 11:34 pm
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 !!!
April 21, 2013 at 11:42 pm
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' 😛 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/
April 22, 2013 at 12:09 am
This was removed by the editor as SPAM
April 22, 2013 at 12:43 am
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.
April 22, 2013 at 2:27 am
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
April 22, 2013 at 2:47 am
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.
April 22, 2013 at 5:45 am
Kapil good going...
Good chance to recall return types & Data type precedence of ISNULL,CONVERT & CASE...
--
Dineshbabu
Desire to learn new things..
April 22, 2013 at 6:15 am
If I am not wrong... This may be easier to understand.
----------------------------------------------------------------------------------------
SELECT ID, ISNULL(CAST(IsAvailable AS VARCHAR), GETDATE()) AS IsAvailable FROM #Test
-----------------------------------------------------------------------------------------
April 22, 2013 at 6:47 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 22, 2013 at 6:59 am
Thanks for the great question.
April 22, 2013 at 10:09 am
You can eliminate options 1 and 2 because they don't name the second result column as IsAvailable, as it is shown in the desired output.
Of course, I wasn't sure if this detail was intentional or not, so I checked all of the queries anyway. 😛
April 23, 2013 at 5:58 am
friends, since i am new to sql, i have little confusion with this query ( i.e. choice number two and four)
Choice two: SELECT ID, COALESCE(IsAvailable, GETDATE()) FROM #Test
choice four: SELECT ID, CASE WHEN IsAvailable IS NULL THEN GETDATE() ELSE IsAvailable END IsAvailable FROM #Test
# now, wat i know coalesce is that it check for first not null, and then why does Isavailable column value 1 gets converted into '1900: .........' it should have been just 1 (i.e. as it is in table )
April 23, 2013 at 6:27 am
Nice Question....
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply