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:
>

Different no. of rows returned in SEM vs QA

Hi,
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
Thanks,
AlphCould you kindly post the query, so that we can help you better.|||Its a union query with 12 Selects. Here is the first select:

SELECT
dbo.tblWBS.PSWBS AS [PSWBS Code],
ConcatenatedWBS AS [CWBS Code],
case when CR is null then 'R' else 'C' end AS [Capital / Revenue],
NDACost AS [Cost Element],
SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) as Amount,
'01-04-' + ltrim(rtrim(str(FiscalYear))) AS [Start Date],
'30-04-' + ltrim(rtrim(str(FiscalYear))) AS [Finish Date],
left(dbo.tblWBS.wbs,6) + Right(dbo.tblWBS.wbs,5) AS [Charge Code]

FROM dbo.tblBCWSMonthly INNER JOIN
dbo.tblBCWSYearly ON dbo.tblBCWSMonthly.RecordId = dbo.tblBCWSYearly.RecordUid INNER JOIN
dbo.tblWBS ON dbo.tblBCWSYearly.WBSUId = dbo.tblWBS.WBSuid

WHERE (dbo.tblWBS.EPSLvl4 = N'1.1.5.17') and dbo.tblWBS.PSWBS = '1.1.5.17.10.01.17001.00000.30 '

GROUP BY dbo.tblWBS.PSWBS, ConcatenatedWBS, CR, NDACost,'01-04-' + ltrim(rtrim(str(FiscalYear))),'30-04-' + ltrim(rtrim(str(FiscalYear))),dbo.tblWBS.wbs

HAVING SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) <> 0

UNION ALL

>> Then another 11 select statements|||Check the "Set concat_null_yields_null" setting in your Query Analyzer Connection Properties dialog box. Try toggling it, as it may be set different than your Server default.