i have datetime
column in table following data:
2011-03-23 2011-04-19 2011-04-26 2011-05-26
i want select distinct mon-yyyy
format output ordered report date descending. need select 1 column in sql statement
this sql works, want order reportdate
column
select distinct substring (convert(varchar, reportdate, 100),1,3) +'-'+ substring (convert(varchar, reportdate, 100),8,4 ) [envelopsdb].[dbo].[envelopes]
output
apr-2011 mar-2011 may-2011
this sql gives error:
select distinct substring (convert(varchar, reportdate, 100),1,3) +'-'+ substring (convert(varchar, reportdate, 100),8,4 ) [envelopsdb].[dbo].[envelopes] order reportdate
error:
msg 145, level 15, state 1, line 2
order items must appear in select list if select distinct specified.
what best sql query output need?
with testdata ( select cast('2011-03-23' datetime) d union select cast('2011-04-19' datetime) union select cast('2011-04-26' datetime) union select cast('2011-05-26' datetime) ) select datename(month,d)+'-'+datename(year,d) testdata group datepart(year,d), datepart(month,d), datename(month,d),datename(year,d) order datepart(year,d), datepart(month,d)
select datename(month,reportdate)+'-'+datename(year,reportdate) [envelopsdb].[dbo].[envelopes] group datepart(year,reportdate), datepart(month,reportdate), datename(month,reportdate),datename(year,reportdate) order datepart(year,reportdate), datepart(month,reportdate)
Comments
Post a Comment