set @sql='select style,factory ,ITEM,ItemNM ,Season,cat'
select @sql=@sql+',sum(case buy_dt when '''+buydt+''' then qty else 0 end) ['+buydt+']' from (select distinct convert(varchar(10),buydt,103) buy_dt from product where dataym=@ym) as a order by buydt
set @sql=@sql+' into ##temptable from (select style,item,ItemNM,Season,cat,Factory,dataym,qty,convert(varchar(10),buydt,103) buydt from product) t where dataYM='+@ym+'group by style,item,ItemNM,Season,factory,cat,dataym'
exec (@sql)
以上语句,动态生成buydt列,字段名是日期,例如:01/11/2012,01/12/2012等等。不跨年的时候没问题,从左到右按照月份大小排列,如果跨年就变成了01/01/2013,01/11/2012,01/12/2012。请教如何在跨年的情况下,让01/01/2013排在2012年后面???
谢谢
select @sql=@sql+',sum(case buy_dt when '''+buydt+''' then qty else 0 end) ['+buydt+']' from (select distinct convert(varchar(10),buydt,103) buy_dt from product where dataym=@ym) as a order by buydt
set @sql=@sql+' into ##temptable from (select style,item,ItemNM,Season,cat,Factory,dataym,qty,convert(varchar(10),buydt,103) buydt from product) t where dataYM='+@ym+'group by style,item,ItemNM,Season,factory,cat,dataym'
exec (@sql)
以上语句,动态生成buydt列,字段名是日期,例如:01/11/2012,01/12/2012等等。不跨年的时候没问题,从左到右按照月份大小排列,如果跨年就变成了01/01/2013,01/11/2012,01/12/2012。请教如何在跨年的情况下,让01/01/2013排在2012年后面???
谢谢
UNION ALL SELECT '2013-06-02'
UNION ALL SELECT '2012-05-06';SELECT CAST((SELECT ', '+CONVERT(varchar,t,101) FROM @a ORDER BY datepart(yy,t),DATEPART(mm,t),DATEPART(dd,t) FOR XML PATH('')) AS NVARCHAR(max))你试一下这个。
order by buydt ---- order by DATEPART(YEAR,buydt)
declare @date varchar(20)
set @date='01/11/2012'
select cast (@date as datetime)
------------------------------------------
/*
2012-01-11 00:00:00.000
(所影响的行数为 1 行)
*/