Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Type conversions and Nulls Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 9:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:04 AM
Points: 2,299, Visits: 1,356
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
Posted Tuesday, February 5, 2013 9:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 3:37 PM
Points: 1,062, Visits: 1,028
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
Posted Tuesday, February 5, 2013 12:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:24 AM
Points: 1,974, Visits: 10,962
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
Posted Tuesday, February 5, 2013 1:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 8,743, Visits: 9,292
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
Post #1416079
Posted Tuesday, February 5, 2013 4:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:23 AM
Points: 3,299, Visits: 1,978
Nice question and explanation. Thanks Tom.
Post #1416143
Posted Tuesday, February 5, 2013 5:54 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: Tuesday, December 10, 2013 4:26 PM
Points: 582, Visits: 451
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
Posted Tuesday, February 5, 2013 7:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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
Posted Tuesday, February 5, 2013 9:44 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: Yesterday @ 6:29 PM
Points: 522, Visits: 555
Curse you Precedence!

always the one that I forget about, ah well :)
Post #1416230
Posted Wednesday, February 6, 2013 3:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:57 AM
Points: 952, Visits: 1,159
Easy one for me .

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
Post #1416353
Posted Wednesday, February 6, 2013 7:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,675, Visits: 72,433
Good question, thank you for writing it!



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1416516
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse