Different results -- sql server programming

oj
2/7/2005 10:35:12 AM

This 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

[quoted text, click to view]

Alejandro Mesa
2/7/2005 10:43:02 AM

Robert,

The diff are here:

(A)
[quoted text, click to view]

(B)
[quoted text, click to view]

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

[quoted text, click to view]

Alejandro Mesa
2/7/2005 10:59:04 AM

I am completely wrong. I missed the "as varchar(2)" part. OJ and Scott posts
explain the problem correctly.

AMB

[quoted text, click to view]

Scott Morris
2/7/2005 1:38:45 PM

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)

[quoted text, click to view]

Robert Richards via SQLMonster.com
2/7/2005 6:19:15 PM

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

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Different results