ORACLE/MSSQL日期函数在报表项目中的应用【日报/周报/月报】

来源：互联网发布：2017ipad视频软件编辑：程序博客网时间：2024/04/27 09:03

报表项目中用到的，获取已知年份的第一天和最后一天，觉得网来的不错，留下做个念想，下次再用。

（一）SQL SERVER 第一天与最后一天

--1.一个月第一天的SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)--2.本周的星期一SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)--3.一年的第一天SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)--4.季度的第一天SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)--5.当天的半夜SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)--6.上个月的最后一天SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))--7.去年的最后一天SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))--8.本月的最后一天SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))--9.本年的最后一天SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))--10.本月的第一个星期一select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

(二)ORACLE

--本周select trunc(sysdate,'d')+1 from dual;select trunc(sysdate,'d')+7 from dual;--本月select trunc(sysdate,'mm') from dual;select last_day(trunc(sysdate)) from dual;--本季select trunc(sysdate,'Q') from dual;select add_months(trunc(sysdate,'Q'),3)-1 from dual;--本年select trunc(sysdate,'yyyy') from dual;select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;-- 星期一到星期六处理方式select trunc(to_date('2011-10-16','yyyy-MM-dd'),'day')+8 from dual;-- 如果是星期天处理方式select trunc(to_date('2011-10-16','yyyy-MM-dd'),'day')+1 from dual;-- 判断是星期几select to_char(to_date('2011-12-26','yyyy-mm-dd'),'day') from dual;