请教两个日期字段，相减得到分的sql语句怎么写？ 在SQL中两个带日期和时间的 相减 如何得到分钟
sql\u4e2d\u4e24\u4e2a\u65f6\u95f4\u5b57\u6bb5\u76f8\u51cf\u7684sql\u8bed\u53e5\u600e\u4e48\u5199DATEDIFF ( datepart , startdate , enddate )
datepart \u65f6\u95f4\u5355\u4f4d
\u8fd9\u4e2a\u662f\u5c0f\u65f6\u5dee datediff(hour,\u5f00\u59cb\u65f6\u95f4,\u7ed3\u675f\u65f6\u95f4)
\u6839\u636e\u9898\u4e3b:
\u5982\u679c\u8981\u5f97\u5230\u5c0f\u6570,
SELECT CAST(DATEDIFF(MINUTE,'2016-01-01 12:10','2016-01-04 14:40 ')AS DECIMAL)/60
\u7531\u4e8e datediff\u5f97\u5230\u7684\u90fd\u662fint,\u6240\u4ee5\u8981\u8f6c\u4e00\u4e0b
\u53c2\u8003:
DATEDIFF (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms189794.aspx
declare @t1 varchar(12)declare @t2 varchar(12) set @t1 = '2018-1-1 10:20:45'set @t2 = '2018-1-24 22:28:45' --@t2\u51cf\u53bb@t1select datediff(mi,@t1,@t2)
DATEDIFF ( datepart , startdate , enddate )datepart 时间单位
这个是小时差 datediff(hour,开始时间,结束时间)
根据题主:
如果要得到小数,
SELECT CAST(DATEDIFF(MINUTE,'2016-01-01 12:10','2016-01-04 14:40 ')AS DECIMAL)/60
由于 datediff得到的都是int,所以要转一下
首先在数据库里建立一张test表（mysql的数据库）
1 CREATE TABLE `NewTable` (
2 `id` int(10) NOT NULL AUTO_INCREMENT ,
3 `time1` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
4 `time2` timestamp NULL DEFAULT NULL ,
5 PRIMARY KEY (`id`)
6 )
然后录入两条数据，用如下语句查询
SELECT time2,time1,time2-time1 FROM `test`;
绛旓細杩欎釜鏄皬鏃跺樊 datediff(hour,寮濮鏃堕棿,缁撴潫鏃堕棿)鏍规嵁棰樹富:濡傛灉瑕佸緱鍒板皬鏁,SELECT CAST(DATEDIFF(MINUTE,'2016-01-01 12:10','2016-01-04 14:40 ')AS DECIMAL)/60 鐢变簬 datediff寰楀埌鐨閮芥槸int,鎵浠ヨ杞竴涓
绛旓細now()-stime 寰楀埌鐨勬槸minute * 100锛岃繖涓暟鍊肩殑涓鍒嗛挓鐨鍗曚綅鎹㈢畻鏄100=1鍒嗛挓锛岃秴杩60鍒嗛挓杩欎釜鏁板兼棤鏁堛寰楀埌鏃堕棿宸細select (unix_timestamp(now())-unix_timestamp(stime))/60 from table_name where whew_condition;mysql> select * from timeminus;+---+---+ | id | stime | +---+...
绛旓細select requestid,userid,workflowid,receivedate,receivetime,operatedate,operatetime,cast(day as varchar)+'澶'+cast((hour-day*24) as varchar)+'灏忔椂'+cast((minute-hour*60) as varchar)+'鍒'+cast((second-minute*60) as varchar)+'绉' resultfrom(select requestid,userid,workflowid,...
绛旓細鍋囪瀛楁1鐨勬椂闂翠负2017/07/20 8:00:00瀛楁2鐨勬椂闂翠负2017/07/20 9:00:00鏃堕棿鐩稿噺鍗冲彲锛歴elect (瀛楁2鏃堕棿-瀛楁1鏃堕棿)/1440 as 鏃堕棿宸 from tableName杩斿洖缁撴灉60
绛旓細鍙互鐢ㄨ繖鏍风殑璇彞锛氬2涓椂闂瀛楁鏄痓egindate锛宔nddate锛屽崟浣嶇敤灏忔椂銆俿elect avg(h) from (select datediff(hh,begindate,enddate) h from table ) a 濡傛灉瑕佹妸鏃堕棿鐩稿姞鐨勫崟浣嶆敼鎴愬ぉ锛屽懆绛夛紝鍙璇atediff閲岀殑hh鏀规垚day锛寃eek灏卞彲浠ヤ簡
绛旓細select datediff(mi,瀛楁1,瀛楁2) from tablename 璇硶 DATEDIFF ( datepart , startdate , enddate )鍙傛暟 datepart 鏄瀹氫簡搴斿湪鏃ユ湡鐨鍝竴閮ㄥ垎璁＄畻宸鐨勫弬鏁般備笅琛ㄥ垪鍑轰簡 Microsoft® SQL Server™ 璇嗗埆鐨勬棩鏈熼儴鍒嗗拰缂╁啓銆傛棩鏈熼儴鍒 缂╁啓 year yy, yyyy quarter qq, q Month mm, m da...
绛旓細select datediff(day, 寮濮鏃ユ湡,缁撴潫鏃ユ湡); --涓ゆ棩鏈闂撮殧澶 select datediff(week, 寮濮嬫棩鏈,缁撴潫鏃ユ湡); --涓ゆ棩鏈熼棿闅斿懆 select datediff(hour, 寮濮嬫棩鏈,缁撴潫鏃ユ湡); --涓ゆ棩鏈熼棿闅斿皬鏃 select datediff(minute, 寮濮嬫棩鏈,缁撴潫鏃ユ湡); --涓ゆ棩鏈熼棿闅斿垎 select datediff(second, 寮濮嬫棩鏈,缁撴潫鏃ユ湡); -...
绛旓細jssj,kssj:TDatetime;寰堝鏄 鍏堣绠楀樊澶氬皯澶 int(jssj-kssj)=鐩稿樊澶╂暟 decodetime(jssj-kssj,h,m,s,ms);鐩稿樊 int(jssj-kssj)澶╋紝h灏忔椂锛宮鍒嗭紝s绉掞紝ms姣 杩欐槸delphi浠ｇ爜锛屼笉鏄痵ql璇彞 鏄 TDateTime绫诲瀷
绛旓細涓や釜Date绫诲瀷瀛楁锛歋TART_DATE锛孍ND_DATE锛岃绠楄繖涓や釜鏃ユ湡鐨鏃堕棿宸紙鍒嗗埆浠ュぉ锛屽皬鏃讹紝鍒嗛挓锛岀锛屾绉掞級锛氬ぉ锛歊OUND(TO_NUMBER(END_DATE - START_DATE))灏忔椂锛歊OUND(TO_NUMBER(END_DATE - START_DATE) * 24)鍒嗛挓锛歊OUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)绉掞細ROUND(TO_NUMBER(...
绛旓細oracle鐨鏃ユ湡绫诲瀷鐩稿噺寰楀埌鐨鏄ぉ锛屽鏋滀綘闇瑕寰楀埌鍒嗛挓锛鍙互杩欐牱锛(date1 - date2)*24*60 濡傛灉浣犵殑瀛楁鏄瓧绗︿覆锛屽厛杞崲鎴愭棩鏈焏ate绫诲瀷锛(to_date(a,'yyyy-mm-dd hh24:mi:ss') - to_date(b,'yyyy-mm-dd hh24:mi:ss'))*24*60 ...
