Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Anthony Kudin » coalesce and nullif
Add to briefcase
19 posts, Page 2 of 2
««12

coalesce and nullif Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Hugo Kornelis
Posted Wednesday, June 23, 2010 8:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 5,293, Visits: 7,229
forjonathanwilson (6/23/2010)
null date can be cast as null string, but not the other way, interesting.

This is not correct. A null string can be cast as date (result will be NULL). The problem starts when you try to case a non-null string as date - in that case, the string must evaluate to a valid date. And obviously, the string 'Test' does not qualify there.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #941807
forjonathanwilson
Posted Wednesday, June 23, 2010 9:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 8:24 AM
Points: 69, Visits: 33
Hugo Kornelis (6/23/2010)
forjonathanwilson (6/23/2010)
null date can be cast as null string, but not the other way, interesting.

This is not correct. A null string can be cast as date (result will be NULL). The problem starts when you try to case a non-null string as date - in that case, the string must evaluate to a valid date. And obviously, the string 'Test' does not qualify there.


thanks for clarifying that for me. I also checked this myself after I made my irroneous assumption:

declare @c varchar(10), @c1 varchar(10),
@d datetime, @d1 datetime
select @c = 'test', @c1 = null, @d = '1/1/10', @d1 = null

select coalesce(nullif(@c,@c),nullif(@d,@d))

select coalesce(nullif(@d,@d),nullif(@c,@c))

where the results are compared null datetime to null string by coalesce, which completes just fine.
Post #941854
UMG Developer
Posted Wednesday, June 23, 2010 11:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,163, Visits: 2,150
Interesting question, thanks.

Thanks Oleg for the explanation of why it happens, I think most people don't already know that, so it is very helpful.
Post #941951
ColdCoffee
Posted Thursday, June 24, 2010 2:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352
Oleg and Hugo , thanks for the nice explanations and links.. nice question depicting the precedence of data-types... niceee.
Post #942298
Anam Verma
Posted Thursday, June 24, 2010 7:18 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:21 PM
Points: 524, Visits: 1,183
thanks for the good QOTD and explanation behind it.
Post #942781
Daniel Bowlin
Posted Monday, June 28, 2010 12:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,681, Visits: 2,423
Good question, thanks. The answer logic seems backwards to me. I think I am going to have to do a little more reading.
Post #944161
SQLRNNR
Posted Wednesday, July 07, 2010 3:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438
Excellent question


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #948866
Yuvaraj S-467280
Posted Monday, August 16, 2010 4:30 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:27 PM
Points: 15, Visits: 59
interesting qotd...thanks for enlighting on the difference

__________________________________________________________
Yuvaraj
Post #969668
L' Eomot Inversé
Posted Saturday, September 18, 2010 1:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 7,182, Visits: 7,281
Fun question.

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware
Post #988777
« Prev Topic | Next Topic »

Add to briefcase
19 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.