Type conversions and Nulls

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tom Thomson » Type conversions and Nulls

49 posts, Page 4 of 5

«««1 2 3 4 5 »»

Type conversions and Nulls

Rate Topic

Display Mode

Topic Options

Author

Message

Miles Neale

Posted Tuesday, February 05, 2013 9:55 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:47 PM
Points: 1,889, Visits: 935

Thanks Tom. After a number of controversial questions and explanations it is great to have one that makes you think and is right on target.

+1

Not all gray hairs are Dinosaurs!

Post #1415965

chgn01

Posted Tuesday, February 05, 2013 9:59 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:10 AM
Points: 698, Visits: 680

Please try:
declare @k int;
select
case when @k=0 OR NOT (@k = 0)
then cast(0 as date)
else cast('2012-04-01T10:00:00' as datetime)
end as [when] ;

and

declare @k int;
select
case when @k=0 OR NOT (@k = 0)
then cast('04/01/2012T10:00:00' as date)
else cast('2012-04-01T10:00:00' as datetime)
end as [when] ;

--------------------------------------
"Stay Hungry, Stay Foolish." ― Jobs
“Everything has beauty, but not everyone sees it.” ― Confucius

Post #1415968

Rob Schripsema

Posted Tuesday, February 05, 2013 12:32 PM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:57 AM
Points: 1,632, Visits: 10,850

Great question, Tom

I almost wish you had thrown in an option of '2012-04-01 10:00:00.000' to spice up the thinking a bit more. Of course, then I probably would have gotten it wrong....

Rob Schripsema
Accelitec, Inc

Post #1416034

L' Eomot Inversé

Posted Tuesday, February 05, 2013 1:52 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:32 PM
Points: 7,080, Visits: 7,125

chgn01 (2/5/2013)

Please try:
declare @k int;
select
case when @k=0 OR NOT (@k = 0)
then cast(0 as date)
else cast('2012-04-01T10:00:00' as datetime)
end as [when] ;

Well, that will fail with an error at cast(0 as date)

and

declare @k int;
select
case when @k=0 OR NOT (@k = 0)
then cast('04/01/2012T10:00:00' as date)
else cast('2012-04-01T10:00:00' as datetime)
end as [when] ;

Well, it seems quite reasonable that cast('2012-04-01T10:00:00' as datetime) should deliver
2012-04-01 10:00:00.000

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1416079

KWymore

Posted Tuesday, February 05, 2013 4:05 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:58 AM
Points: 2,570, Visits: 1,530

Nice question and explanation. Thanks Tom.

Post #1416143

Olga B

Posted Tuesday, February 05, 2013 5:54 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 22, 2013 5:58 PM
Points: 561, Visits: 440

Well, the good news is, I got NULLs down pat. The less good news is, I didn't even stop to think about type precedence.

Conclusion - time for more coffee.

Post #1416183

john.arnott

Posted Tuesday, February 05, 2013 7:10 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008

Olga B (2/5/2013)

Well, the good news is, I got NULLs down pat. The less good news is, I didn't even stop to think about type precedence.

Conclusion - time for more coffee.

+1 on that.
And as for the collation specification, I figured that was just Tom's QOD signature since most of his contributions that I recall had to do with that.

Overall, very good one.

Post #1416204