May 17, 2012 at 2:04 pm
Hello All,
I have the following query:
I am storing the start month in a variable called @StartMonth. Then I want to cast 'month1' as the month in the @StartMonth
select ISNULL(AVG(month1),0) AS DATENAME(month,@StartMonth)
The date store is 'Jul 1 2008 12:00AM', so you can also try the following, but it complains: "incorrect syntac near 'month'. What is wrong with the following?
select ISNULL(AVG(month1),0) AS DATENAME(month,'Jul 1 2008 12:00AM')
Thank you,
Rama
May 17, 2012 at 2:06 pm
I don't get what you're trying to do with "AS" there, and neither does SQL Server.
"AS" in a Select statement indicates a column alias, but you have a data function there. What are you trying to do with that? What's the expected end-result?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 17, 2012 at 2:14 pm
I want to select as:
select ISNULL(AVG(month1),0) AS 'JULY'
but 'JULY' depends on a variable @StartMonth
May 17, 2012 at 2:15 pm
Looks like the OP is trying to dynamically name the column alias based on a specific value.
May 17, 2012 at 2:33 pm
What does "P" stand for?
May 17, 2012 at 2:38 pm
ramadesai108 (5/17/2012)
What does "P" stand for?
Was supposed to be "OP", but apparently I didn't it the O hard enough. OP means Original Poster in this instance.
May 17, 2012 at 2:38 pm
OP = Original poster. in this case - you:-)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 17, 2012 at 2:56 pm
I figured out the solution. It was very simple but my brain was not working:
Set @Mth1 = DATENAME(month,@StartDate)
and then use @Mth1 in my dynamic query.
May 17, 2012 at 3:15 pm
A possible valid code should look like:
declare @StartMonth Datetime = 'Jul 1 2008 12:00AM'
declare @query varchar(2048) = 'select'
select ISNULL(AVG(month(@StartMonth)),0), DATENAME(Month, @StartMonth);
select ISNULL(AVG(month(@StartMonth)),0), DATENAME(month,'Jul 1 2008 12:00AM');
set @query = @query + ' ISNULL(AVG(month(''' + convert(varchar, @StartMonth) + ''')),0) ' + 'as ' + DATENAME(Month, @StartMonth)
print @query;
exec sp_sqlexec @query;
🙂
May 18, 2012 at 8:52 am
For a dynamic column name like that, you'll either need to use an Unpivot statement that lists the months, or you'll need to use dynamic SQL. Sounds like you went with dynamic SQL, which is what I would probably have done too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply