Type conversions and Nulls
-
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] ;
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius -
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
Propack, Inc. -
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
-
Nice question and explanation. Thanks Tom.
-
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.
-
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.
-
Curse you Precedence!
always the one that I forget about, ah well
-
Easy one for me .
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
-
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[/url]
For tips on how to post your problems[/url] -
Nice question - thanks, Tom!
-
Thanks for the question!
Best Regards,
Chris Büttner
-
Rob Schripsema (2/5/2013)
Great question, TomI 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....
+1 to that
Nice question.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Wow, my first really wrong answer. I knew about CASE's type inferral, just forgot. Thanks for the exceptionally good question.
-
I got caught by the Null. I work as a Programmer more often than not where some Languages automatically initialise a Variable at declaration time. The ones that don't tend to just leave the Variable with whatever is in its memory location. I would prefer Null as a default in these instances rather than garbage.
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply