基于yyyymmdd的当前月份和去年同月的检索

Question

有什么方法可以根据yyyymmdd来检索今年和去年同一个月的月份吗？我的报告是比较去年月份和本年度月份的数据。例如，如果yyyymmdd是20180613，那么我希望有日期20180601到20180613和20170601到20170613。

会很感激你给我的建议和帮助。

谢谢!

Answer 1

SQL Server

你可以试试Convert(type,datetime,112)和DATEADD

declare @tdate DATETIME= CONVERT(DATETIME, '20180613', 112) ;
select CONVERT(nvarchar(20), sDate1, 112) sDate1,
    CONVERT(nvarchar(20), eDate1, 112) eDate1,
    CONVERT(nvarchar(20), sDate2, 112) sDate2,
    CONVERT(nvarchar(20), eDate2, 112) eDate2
from (
    SELECT DATEADD(day, - DAY ( @tdate ) + 1 ,  @tdate) sDate1,
        @tdate eDate1,
        DATEADD(year,-1,DATEADD(day, - DAY ( @tdate ) + 1 ,  @tdate)) sDate2,
        DATEADD(year,-1,@tdate) eDate2
)T;

结果：

sDate1               eDate1               sDate2               eDate2
-------------------- -------------------- -------------------- --------------------
20180601             20180613             20170601             20170613

测试演示链接

Answer 2

在甲骨文里-

查询-

select to_char(trunc(sysdate),'yyyymmdd') as y1, to_char(trunc(sysdate,'mm'),'yyyymmdd') as y2, to_char(trunc(sysdate) + interval '-1' year,'yyyymmdd') as y3, to_char(trunc(sysdate,'mm') + interval '-1' year,'yyyymmdd') as y4 from dual;

产出-

"Y1","Y2","Y3","Y4"
"20180613","20180601","20170613","20170601"