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!!
- Edited by Anil Dangol 7 hours 54 minutes ago