March 19, 2009 at 8:26 am
I have query which return as it in sql but if i enable the groub by it unabel to execute
error showing.....
"Msg 8120, Level 16, State 1, Line 40
Column 'MF_NAVDetails.NAVDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
"
if works if I format CONVERT(varchar(20),A.NAVDate,103) As NAVDATE,
in both group by and in Order by clause..
My req is to format date using varchar but display by datetime
pl help.....
---------------------------------------
SELECT B.SchemeCode,B.SchemeName,CONVERT(varchar(20),A.NAVDate,103) As NAVDATE,
A.NAVAmount,A.PreviousDayNAV,A.Change
FROM MF_NAVDetails A INNER JOIN MF_SchemeMaster B
ON A.SchemeID = B.SchemeID
WHERE
B.AMCID = 6 AND B.SchemeID = 106 AND
(A.NAVDate BETWEEN '2006-08-01' AND '2009-08-30') AND
A.DeleteFlag = 0
--group by B.SchemeCode,B.SchemeName, CONVERT(DATETIME,A.NAVDate,103) ,A.NAVAmount,A.PreviousDayNAV,A.Change
order by CONVERT(DATETIME,A.NAVDate,103) desc
March 19, 2009 at 9:29 am
If you're going to post, you need to at least spell check and proof read my friend....
I think you want this:
SELECT B.SchemeCode ,
B.SchemeName ,
CONVERT(VARCHAR(20),CONVERT(DATETIME,A.NAVDate,103),103) AS NAVDATE,
A.NAVAmount ,
A.PreviousDayNAV ,
A.Change
FROM MF_NAVDetails A
INNER JOIN MF_SchemeMaster B
ON A.SchemeID = B.SchemeID
WHERE B.AMCID = 6
AND B.SchemeID = 106
AND
(
A.NAVDate BETWEEN '2006-08-01' AND '2009-08-30'
)
AND A.DeleteFlag = 0
group by B.SchemeCode,B.SchemeName, CONVERT(DATETIME,A.NAVDate,103) ,A.NAVAmount,A.PreviousDayNAV,A.Change
ORDER BY CONVERT(DATETIME,A.NAVDate,103) DESC
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply