Is it a bug or a feature?

Today, I found a very strange result when I executed one of my queries
Declare @xml varchar(max)
Declare @StartingDate datetime,@EndDate Date,@Year varchar(10),@Month varchar(10),@Day varchar(10),@ShareLocation varchar(255)
Set @StartingDate='2013-07-01'
Set @Year=Year(@StartingDate)
Select @Month=
Case 
When cast(Month(@StartingDate) as Int) between 1 and 9 then '0'+cast(Month(@StartingDate) as varchar)
else Month(@StartingDate) 
End
Select @day= 
Case 
When cast(Day(@StartingDate) as Int) between 1 and 9 then '0'+cast(Day(@StartingDate) as varchar)
else Day(@StartingDate) 
End

Select @month,@day

What would you think the result would be? I was expecting '07' and '01'

But Wrong!!! It's giving me 7 and 1 only. It ignores 0

Now I modified a query little bit and put 'slkjfskjfsjf' at the else like this

Declare @xml varchar(max)
Declare @StartingDate datetime,@EndDate Date,@Year varchar(10),@Month varchar(10),@Day varchar(10),@ShareLocation varchar(255)
Set @StartingDate='2013-07-01'
Set @Year=Year(@StartingDate)
Select @Month=
Case 
When cast(Month(@StartingDate) as Int) between 1 and 9 then '0'+cast(Month(@StartingDate) as varchar)
else 'slkjfskjfsjf' 
End
Select @day= 
Case 
When cast(Day(@StartingDate) as Int) between 1 and 9 then '0'+cast(Day(@StartingDate) as varchar)
else 'slkjfskjfsjf' 
End

Select @month,@day

Now I am getting '07','01' . Well what just happened? Else is determining the the output of when clause.

What I found was even though it didn't go through else statement actually it did. In former case, my output of else statement is int so it converted the output of when clause to int even though i converted to varchar but it internally casted to int. It's because of else statement's output is int. 

In second example, else statement's output is varchar, so it type casted the output of when statement's output to varchar.

Very strange!!


August 20th, 2015 7:15pm

Hi,

Try this

Declare @xml varchar(max)
Declare @StartingDate datetime,@EndDate Date,@Year varchar(10),@Month varchar(10),@Day varchar(10),@ShareLocation varchar(255)
Set @StartingDate='2013-07-01'
Set @Year=Year(@StartingDate)
Select @Month=
Case 
When cast(Month(@StartingDate) as Int) between 1 and 9 then '0'+ cast(Month(@StartingDate) as varchar)
else CAST(Month(@StartingDate) AS VARCHAR)
End
Select @day= 
Case 
When cast(Day(@StartingDate) as Int) between 1 and 9 then '0'+cast(Day(@StartingDate) as varchar)
else CAST(Day(@StartingDate) AS VARCHAR)
End

Select @month,@day

August 21st, 2015 1:29am

Thanks but I do think that it should give us some kind of warning before casting. Also I tried Month(@Date) as varchar and it worked fine. 

Sometime when we have very complex SQL code it would be very difficult for devs to debug code when language itself does some hidden automatic things. I am not saying it shouldn't do it but at least give us some information or warning.

Anyway Cheers :) and thanks for the link :)

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 2:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics