56,678
社区成员
发帖
与我相关
我的任务
分享
SQL> select * from V$VERSION ;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL> select sysdate,
2 TRUNC (sysdate,'MONTH'),
3 ADD_MONTHS(TRUNC (sysdate,'MONTH'),1)
4 from dual;
SYSDATE TRUNC(SYS ADD_MONTH
--------- --------- ---------
27-FEB-09 01-FEB-09 01-MAR-09
select CURDATE(),
CURDATE()-DAYOFMONTH(CURDATE())+1,
DATE_ADD(CURDATE()-DAYOFMONTH(CURDATE())+1, INTERVAL 1 MONTH),
UNIX_TIMESTAMP(CURDATE()-DAYOFMONTH(CURDATE())+1),
UNIX_TIMESTAMP(DATE_ADD(CURDATE()-DAYOFMONTH(CURDATE())+1, INTERVAL 1 MONTH));
mysql> select CURDATE(),
-> CURDATE()-DAYOFMONTH(CURDATE())+1,
-> DATE_ADD(CURDATE()-DAYOFMONTH(CURDATE())+1, INTERVAL 1 MONTH),
-> UNIX_TIMESTAMP(CURDATE()-DAYOFMONTH(CURDATE())+1),
-> UNIX_TIMESTAMP(DATE_ADD(CURDATE()-DAYOFMONTH(CURDATE())+1, INTERVAL 1 MONTH));
+------------+-----------------------------------+---------------------------------------------------------------+---------------------------------------------------+-------------------------------------------------------------------------------+
| CURDATE() | CURDATE()-DAYOFMONTH(CURDATE())+1 | DATE_ADD(CURDATE()-DAYOFMONTH(CURDATE())+1, INTERVAL 1 MONTH) | UNIX_TIMESTAMP(CURDATE()-DAYOFMONTH(CURDATE())+1) | UNIX_TIMESTAMP(DATE_ADD(CURDATE()-DAYOFMONTH(CURDATE())+1, INTERVAL 1 MONTH)) |
+------------+-----------------------------------+---------------------------------------------------------------+---------------------------------------------------+-------------------------------------------------------------------------------+
| 2009-02-27 | 20090201 | 2009-03-01 | 1233417600 | 1235836800 |
+------------+-----------------------------------+---------------------------------------------------------------+---------------------------------------------------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
--SQL Server
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')