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 «««12345»»

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, November 21, 2014 10:15 AM
Points: 2,464, Visits: 1,551
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: Friday, November 21, 2014 11:01 AM
Points: 1,113, Visits: 1,089
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:51 PM
Points: 2,010, Visits: 10,969
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 7,860, Visits: 9,606
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: Yesterday @ 12:42 PM
Points: 3,388, Visits: 2,021
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: Friday, September 12, 2014 4:57 PM
Points: 582, Visits: 453
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, 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: Tuesday, September 23, 2014 6:47 PM
Points: 523, Visits: 557
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: Today @ 3:41 AM
Points: 968, Visits: 1,172
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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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 «««12345»»

Permissions Expand / Collapse