SQL语言日期问题
发布网友
发布时间：2022-04-10 20:48
共2个回答
热心网友
时间：2022-04-10 22:18
insert into d values(2,'2010-08-12','2010-10-10')
create table d (no int ,startdate datetime,enddate datetime)
select no,datediff(yy,startdate,enddate)from d 查询两个日期的年份差
select no,datediff(mm,startdate,enddate)from d 查询两个日期的月份差
select no,datediff(dd,startdate,enddate)from d 查询两个日期的天数差
如果要计算租金在天数差的表达式上乘以X即可
select no,datediff(dd,startdate,enddate)*X from d 查询两个日期的租金
热心网友
时间：2022-04-10 23:36
Oracle
两日期相减，单位是“天”
SQL> SELECT
2 TO_DATE('2012-12-21', 'YYYY-MM-DD') - sysdate AS have_dates
3 FROM
4 al;
HAVE_DATES
----------
842.234896
SQL Server
1> SELECT '年' AS '日期部分', DATEDIFF(yy, '2012-12-21', GETDATE()) AS [数值
]
2> UNION ALL
3> SELECT '季度' AS '日期部分', DATEDIFF(qq, '2012-12-21', GETDATE()) AS [数
值]
4> UNION ALL
5> SELECT '月' AS '日期部分', DATEDIFF(mm, '2012-12-21', GETDATE()) AS [数值]
6> UNION ALL
7> SELECT '日' AS '日期部分', DATEDIFF(dd, '2012-12-21', GETDATE()) AS [数值]
8> UNION ALL
9> SELECT '周' AS '日期部分', DATEDIFF(ww, '2012-12-21', GETDATE()) AS [数值
]
10> UNION ALL
11> SELECT '时' AS '日期部分', DATEDIFF(hh, '2012-12-21', GETDATE()) AS [数
值]
12> UNION ALL
13> SELECT '分' AS '日期部分', DATEDIFF(mi, '2012-12-21', GETDATE()) AS [数
值]
14> UNION ALL
15> SELECT '秒' AS '日期部分', DATEDIFF(ss, '2012-12-21', GETDATE()) AS [数
值]
16> go
日期部分 数值
---- -----------
年 -2
季度 -9
月 -28
日 -843
周 -120
时 -20214
分 -1212810
秒 -72768599
(8 行受影响)
MySQL
DATEDIFF 只计算到天的单位
TIMEDIFF 精确到毫秒单位
mysql> SELECT DATEDIFF (
-> '2000-01-01 00:00:00.000000',
-> '2000-01-02 05:06:07.000008') A;
+------+
| A |
+------+
| -1 |
+------+
1 row in set (0.00 sec)
mysql> SELECT
-> TIMEDIFF ('2000-01-01 00:00:00.000000',
-> '2000-01-02 05:06:07.000008') A;
+------------------+
| A |
+------------------+
| -29:06:07.000008 |
+------------------+
1 row in set (0.00 sec)
