日期格式处理

月

WITH Sequence
AS
(
SELECT CONVERT(varchar(10),'2017-12-01',23) dtime
UNION ALL
SELECT
CONVERT(varchar(10),DATEADD(mm,-1,dtime),23)
FROM Sequence
WHERE LEFT(CONVERT(varchar,DATEADD(mm,-1,dtime),21),7)>=LEFT(CONVERT(varchar,'2017-01',21),7)
)
select LEFT(CONVERT(varchar,s.dtime,21),7) as datatime,a.oneNum,a.secondNum from Sequence s left join (
select LEFT(CONVERT(varchar,TraDate,21),7) as datatime,SUM(LocalCount) as oneNum,SUM(OutCount) as secondNum
from dbo.TrafficFlow_staticsByDay where LEFT(CONVERT(varchar,TraDate,120),4)=2017 and BGStationCode='GM_08'
group by LEFT(CONVERT(varchar,TraDate,21),7)
) a on LEFT(CONVERT(varchar,s.dtime,21),7)=a.datatime

 

日

WITH Sequence
AS
(
SELECT CONVERT(varchar(10),'2017-01-31',23) dtime
UNION ALL
SELECT
CONVERT(varchar(10),DATEADD(dd,-1,dtime),23)
FROM Sequence
WHERE LEFT(CONVERT(varchar,DATEADD(dd,-1,dtime),21),7)>=LEFT(CONVERT(varchar,'2017-01',21),7)
)
select LEFT(CONVERT(varchar,s.dtime,21),10) as datatime,a.oneNum,a.secondNum from Sequence s left join (
select LEFT(CONVERT(varchar,TraDate,21),10) as datatime,SUM(LocalCount) as oneNum,SUM(OutCount) as secondNum
from dbo.TrafficFlow_staticsByDay where LEFT(CONVERT(varchar,TraDate,120),4)=2017 and BGStationCode='GM_08'
group by LEFT(CONVERT(varchar,TraDate,21),10)
) a on LEFT(CONVERT(varchar,s.dtime,21),10)=a.datatime