Re: Variable as Alias Name

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

@friday_as_of_date must be a string in order to concatenate the value with the remainder of the SQL string.

I'm not sure what sort of date format string you want but you will also need enclose the value if it doesn't conform to identifier naming rules.

DECLARE @Friday_as_of_date_alias char(8);
SET @friday_as_of_date_alias = '[' + CONVERT(char(8), @friday_as_of_date_alias, 112) + ']';

Also, I suggest you use sp_executesql sp that you can pass @org_level_1_name as a parameter instead of building the value into the SQL statement itself.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"James" <James@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5E00C08A-3456-4B15-8575-B5BDD9A28B89@xxxxxxxxxxxxxxxx
I would like to use the @friday_as_of_date variable for a column alias name
in the code below. I am thinking i have to use dynamic sql to accomplsih
this. Is this the only solution? I am having trouble with the single quotes
around the constants and I am getting the following error:
Conversion failed when converting datetime from character string.

here is the code Ive been trying to get to work:

create procedure dbo.usp_delinquencies_summary_v2
@org_level_1_name varchar(55),
@as_of_date datetime = null
as
declare @friday_as_of_date datetime
declare @sqlstring varchar(4000)

if @as_of_date is not null
set @friday_as_of_date = cast(floor(cast(@as_of_date as float)) as datetime)
else
begin
set @friday_as_of_date =
case datename(dw,getdate())
when 'Saturday' then dateadd(d,-1,getdate())
when 'Sunday' then dateadd(d,-2,getdate())
when 'Monday' then dateadd(d,-3,getdate())
when 'Tuesday' then dateadd(d,-4,getdate())
when 'Wednesday' then dateadd(d,-5,getdate())
when 'Thursday' then dateadd(d,-6,getdate())
when 'Friday' then getdate()
end

set @friday_as_of_date = cast(floor(cast(@friday_as_of_date as float)) as
datetime)
end

set @sqlstring =
'
SELECT
u.AsOfDate,
h.OrganizationalLevel1Name,
h.OrganizationalLevel2Name,
u.BookBalanceAmount AS ' + @friday_as_of_date +
'
FROM dbo.Utilization AS u

LEFT JOIN dbo.vw_CM_T_ORG_HIER_EOM h
ON u.CostCenter = h.CostCenter

WHERE
u.AsOfDate = ' + @friday_as_of_date +
'
and u.BookBalanceAmount <> 0
and h.OrganizationalLevel1Name = ' + @org_level_1_name +
'
GROUP BY u.AsOfDate, h.OrganizationalLevel1Name, h.OrganizationalLevel2Name

ORDER BY h.OrganizationalLevel2Name'

exec(@sqlstring)



.

Relevant Pages

  • Re: Dates
    ... The SQL query is a string, so the date in it is a part of the string, not a separate DateTime value. ... Frederik Vanderhaeghe wrote: ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Failed to convert parameter value from a String to a DateTime
    ... perhaps the error occurs because SQL Server defaults to nvarchar when a parameter is NULL. ... I would try to declare the parameter as DateTime before to assign it the DBNull.Value. ... value may be a valid date such as '01/01/2007' or a null string. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: BETWEEN in dynamic SQL
    ... I have a mess in printed SQL. ... > Instead of EXEC sp_executesql @sql ... >> Syntax error converting datetime from character string. ...
    (microsoft.public.sqlserver.programming)
  • the safest way of handling datetime between asp.net and ms sql
    ... I know that a problem of different datetime strings formatting between ... asp.net and ms sql has been covered here widely, ... Let's say I'm keeping datetime variable in a session. ... cast it - how can I prevent it from casting it to string and inserting ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Access scripts in "Community Submitted Scripts" by Kent Finkle Q2
    ... For some reason the SQL call gets mangled and can't be read ... objConnection.Execute SqlString ... thanks for the feedback but this time no ... is typed in as if it were a string ...
    (microsoft.public.windows.server.scripting)