备用sql语句

上月末：
ksql:
select dateadd(day,-1*dayofmonth(now()),now())
DB2_UDB:
SELECT CURRENT TIMESTAMP + (-1 * DAY(CURRENT TIMESTAMP)) DAY FROM SYSIBM.SYSDUMMY1
MS_SQL_Server:
SELECT DATEADD(day, (-1 * DATEPART(DAY, GETDATE())), GETDATE())
Oracle:
SELECT (SYSDATE + TRUNC((-1 * TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))))) FROM DUAL
Oracle9:
SELECT (SYSDATE + TRUNC((-1 * TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))))) FROM DUAL

本月初：

ksql:
select dateadd(day,-1*(dayofmonth(now())-1),now())
DB2_UDB:
SELECT CURRENT TIMESTAMP + (-1 * (DAY(CURRENT TIMESTAMP) - 1)) DAY FROM SYSIBM.SYSDUMMY1
MS_SQL_Server:
SELECT DATEADD(day, (-1 * (DATEPART(DAY, GETDATE()) - 1)), GETDATE())
Oracle:
SELECT (SYSDATE + TRUNC((-1 * (TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) - 1)))) FROM DUAL
Oracle9:
SELECT (SYSDATE + TRUNC((-1 * (TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) - 1)))) FROM DUAL

本月末：
ksql:
select dateadd(month,1,dateadd(day,-1*dayofmonth(now()),now()))
DB2_UDB:
SELECT CURRENT TIMESTAMP + (-1 * DAY(CURRENT TIMESTAMP)) DAY + 1 MONTH FROM SYSIBM.SYSDUMMY1
MS_SQL_Server:
SELECT DATEADD(month, 1, DATEADD(day, (-1 * DATEPART(DAY, GETDATE())), GETDATE()))
Oracle:
SELECT ADD_MONTHS((SYSDATE + TRUNC((-1 * TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))))), TRUNC(1 )) FROM DUAL
Oracle9:
SELECT ADD_MONTHS((SYSDATE + TRUNC((-1 * TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))))), TRUNC(1 )) FROM DUAL

上一年末:

ksql:
select dateadd(day,-1*dayofyear(now()),now())
DB2_UDB:
SELECT CURRENT TIMESTAMP + (-1 * DAYOFYEAR(CURRENT TIMESTAMP)) DAY FROM SYSIBM.SYSDUMMY1
MS_SQL_Server:
SELECT DATEADD(day, (-1 * DATEPART(DY, GETDATE())), GETDATE())
Oracle:
SELECT (SYSDATE + TRUNC((-1 * TO_NUMBER(TO_CHAR(SYSDATE, 'DDD'))))) FROM DUAL
Oracle9:
SELECT (SYSDATE + TRUNC((-1 * TO_NUMBER(TO_CHAR(SYSDATE, 'DDD'))))) FROM DUAL

上季度末： （月份必须为3,6,9,12）

ksql:
select  dateadd(mm,-2,dateadd(day,-15,convert(DATETIME,'2009-6-15')))
DB2_UDB:
SELECT TIMESTAMP('2009-6-15') + -15 DAY + -2 MONTH FROM SYSIBM.SYSDUMMY1
MS_SQL_Server:
SELECT DATEADD(mm, -2, DATEADD(day, -15, CONVERT(DATETIME, '2009-6-15')))
Oracle:
SELECT ADD_MONTHS((TO_DATE('2009-6-15', 'YYYY-MM-DD HH24:MI:SS') + TRUNC(-15)), TRUNC(-2 )) FROM DUAL
Oracle9:
SELECT ADD_MONTHS((TO_DATE('2009-6-15', 'YYYY-MM-DD HH24:MI:SS') + TRUNC(-15)), TRUNC(-2 )) FROM DUAL