Different results

I am trying to return a two character result for the day number and was
wondering why I get a one character day returned in Statement A, and a two
character result in Statement B?
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
day(getdate())
end
print @.day
--the result is a one character day, if the date was February 7, 2005, the
result = 7
--Statement B:
if len(day(getdate())) = 1
set @.day = '0' + cast (day(getdate()) as varchar(1))
else
set @.day = day(getdate())
print @.day
--the result is a two character day, if the date was February 7, 2005, the
result = 07
Message posted via http://www.webservertalk.comThis is because of implicit conversion and datatype precedence for case/when
statement. Your 'else' clause has higher precedence (INT). Thus, your TRUE
(varchar(2)) clause has to be implicitly converted to INT (i.e. '07' -> 7).
This is the fix.
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
cast(day(getdate()) as varchar(2)) --explicit conversion to
varchar
end
print @.day
And here is a trick without case/when or if/else:
e.g.
set @.day = right(day(getdate())+100,2)
print @.day
-oj
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
>I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||The answer - don't use implicit conversions. And read BOL about the CASE
expression and how it determines the datatype of the returned value. Below
is a quick script that demonstrates two much easier ways to accomplish the
task.
declare @.test datetime
set @.test = '20050115'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
set @.test = '20050102'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||Robert,
The diff are here:
(A)
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
(B)
> set @.day = '0' + cast (day(getdate()) as varchar(1))
In 'A' you are casting the whole expression to varchar(1), that is why you
get 1 character.
Another way of doing this is:
set @.day = right('0' + ltrim(day(getdate())), 2)
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com
>|||I am completely wrong. I missed the "as varchar(2)" part. OJ and Scott post
s
explain the problem correctly.
AMB
"Alejandro Mesa" wrote:
> Robert,
> The diff are here:
> (A)
> (B)
> In 'A' you are casting the whole expression to varchar(1), that is why you
> get 1 character.
> Another way of doing this is:
> set @.day = right('0' + ltrim(day(getdate())), 2)
>
> AMB
>
> "Robert Richards via webservertalk.com" wrote:
>