blocks|key|1914332|text|MsSql语法:+DATEDIFF+(日期部分，开始日期，结束日期)|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1914333|Oracle：这将返回天数|1914334|++++select
++round(Second_date+-+First_date)++as+Diff_InDays,round+((Second_date+-+First_date)+/+(30),1)++as+Diff_InMonths,round+((Second_date+-+First_date)+*+(60*24),2)++as+TimeIn_Minitues
from
++(
++select
++++to_date('01/01/2012+01:30:00+PM','mm/dd/yyyy+hh:mi:ss+am')+as+First_date
+++,to_date('05/02/2012+01:35:00+PM','mm/dd/yyyy+HH:MI:SS+AM')+as+Second_date
++from
++++dual
++)+result;|code-block|syntax|javascript|1914335|演示：http://sqlfiddle.com/#!4/c26e8/36|1914336|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!4/c26e8/36^0|0|8|0|0|6|0|0|3|X|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|D|@$9|14|A|15|1|16]]|E|$]]|$1|O|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

MsSql Syntax : DATEDIFF ( datepart , startdate , enddate )

Oracle: This will returns number of days

<pre><code> select
 round(Second_date - First_date) as Diff_InDays,round ((Second_date - First_date) / (30),1) as Diff_InMonths,round ((Second_date - First_date) * (60*24),2) as TimeIn_Minitues
from
 (
 select
 to_date('01/01/2012 01:30:00 PM','mm/dd/yyyy hh:mi:ss am') as First_date
 ,to_date('05/02/2012 01:35:00 PM','mm/dd/yyyy HH:MI:SS AM') as Second_date
 from
 dual
 ) result;
</code></pre>

Demo : <a href="http://sqlfiddle.com/#!4/c26e8/36" rel="nofollow">http://sqlfiddle.com/#!4/c26e8/36</a>

blocks|key|1914461|text|SELECT+++(MONTHS_BETWEEN(date2,date1)+%2B++(datediff(day,date2,date1))/30)+as+num_months,
datediff(day,date2,date1)+as+diff_in_days++FROM++dual;

//+You+should+replace+date2+with+TO_DATE('2012/03/25',+'YYYY/MM/DD')
//+You+should+replace+date1+with+TO_DATE('2012/01/01',+'YYYY/MM/DD')
//+To+get+you+results|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1914462|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT (MONTHS_BETWEEN(date2,date1) + (datediff(day,date2,date1))/30) as num_months,
datediff(day,date2,date1) as diff_in_days FROM dual;

// You should replace date2 with TO_DATE('2012/03/25', 'YYYY/MM/DD')
// You should replace date1 with TO_DATE('2012/01/01', 'YYYY/MM/DD')
// To get you results
</code></pre>

blocks|key|1914352|text|select+
++dt1,+dt2,
++trunc(+months_between(dt2,dt1)+)+mths,+
++dt2+-+add_months(+dt1,+trunc(months_between(dt2,dt1))+)+days
from
(
++++select+date+'2012-01-01'+dt1,+date+'2012-03-25'+dt2+from+dual+union+all
++++select+date+'2012-01-01'+dt1,+date+'2013-01-01'+dt2+from+dual+union+all
++++select+date+'2012-01-01'+dt1,+date+'2012-01-01'+dt2+from+dual+union+all
++++select+date+'2012-02-28'+dt1,+date+'2012-03-01'+dt2+from+dual+union+all
++++select+date+'2013-02-28'+dt1,+date+'2013-03-01'+dt2+from+dual+union+all
++++select+date+'2013-02-28'+dt1,+date+'2013-04-01'+dt2+from+dual+union+all
++++select+trunc(sysdate-1)++dt1,+sysdate+++++++++++++++from+dual
)+sample_data|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1914353|结果：|unstyled|1914354|%7C++++++++++++++++++++++++DT1+%7C+++++++++++++++++++++++DT2+%7C+MTHS+%7C+++++DAYS+%7C
----------------------------------------------------------------------------
%7C++January,+01+2012+00:00:00+%7C+++March,+25+2012+00:00:00+%7C++++2+%7C+++++++24+%7C
%7C++January,+01+2012+00:00:00+%7C+January,+01+2013+00:00:00+%7C+++12+%7C++++++++0+%7C
%7C++January,+01+2012+00:00:00+%7C+January,+01+2012+00:00:00+%7C++++0+%7C++++++++0+%7C
%7C+February,+28+2012+00:00:00+%7C+++March,+01+2012+00:00:00+%7C++++0+%7C++++++++2+%7C
%7C+February,+28+2013+00:00:00+%7C+++March,+01+2013+00:00:00+%7C++++0+%7C++++++++1+%7C
%7C+February,+28+2013+00:00:00+%7C+++April,+01+2013+00:00:00+%7C++++1+%7C++++++++1+%7C
%7C+++August,+14+2013+00:00:00+%7C++August,+15+2013+05:47:26+%7C++++0+%7C+1.241273+%7C|1914355|测试链接：SQLFiddle|offset|length|1914356|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!4/d41d8/15878^0|0|0|0|5|9|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|V|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|F|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

<pre><code>select 
 dt1, dt2,
 trunc( months_between(dt2,dt1) ) mths, 
 dt2 - add_months( dt1, trunc(months_between(dt2,dt1)) ) days
from
(
 select date '2012-01-01' dt1, date '2012-03-25' dt2 from dual union all
 select date '2012-01-01' dt1, date '2013-01-01' dt2 from dual union all
 select date '2012-01-01' dt1, date '2012-01-01' dt2 from dual union all
 select date '2012-02-28' dt1, date '2012-03-01' dt2 from dual union all
 select date '2013-02-28' dt1, date '2013-03-01' dt2 from dual union all
 select date '2013-02-28' dt1, date '2013-04-01' dt2 from dual union all
 select trunc(sysdate-1) dt1, sysdate from dual
) sample_data
</code></pre>

Results:

<pre><code>| DT1 | DT2 | MTHS | DAYS |
----------------------------------------------------------------------------
| January, 01 2012 00:00:00 | March, 25 2012 00:00:00 | 2 | 24 |
| January, 01 2012 00:00:00 | January, 01 2013 00:00:00 | 12 | 0 |
| January, 01 2012 00:00:00 | January, 01 2012 00:00:00 | 0 | 0 |
| February, 28 2012 00:00:00 | March, 01 2012 00:00:00 | 0 | 2 |
| February, 28 2013 00:00:00 | March, 01 2013 00:00:00 | 0 | 1 |
| February, 28 2013 00:00:00 | April, 01 2013 00:00:00 | 1 | 1 |
| August, 14 2013 00:00:00 | August, 15 2013 05:47:26 | 0 | 1.241273 |
</code></pre>

Link to test: <a href="http://sqlfiddle.com/#!4/d41d8/15878" rel="noreferrer">SQLFiddle</a>

blocks|key|1914482|text|查询如下(假设@dt1+>=+@dt2)；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1914483|Declare+@dt1+datetime+=+'2013-7-3'
Declare+@dt2+datetime+=+'2013-5-2'

select+abs(DATEDIFF(DD,+@dt2,+@dt1))+Days,
case+when+@dt1+>=+@dt2
++++then+case+when+DAY(@dt2)<=DAY(@dt1)
++++++++then+Convert(varchar,+DATEDIFF(MONTH,+@dt2,+@dt1))+%2B+CONVERT(varchar,+'+Month(s)+')+%2B+Convert(varchar,+DAY(@dt1)-DAY(@dt2))+%2B+CONVERT(varchar,+'Day(s).')
++++++++else+Convert(varchar,+DATEDIFF(MONTH,+@dt2,+@dt1)-1)+%2B+CONVERT(varchar,+'+Month(s)+')+%2B+convert(varchar,+abs(DATEDIFF(DD,+@dt1,+DateAdd(Month,+-1,+@dt1)))+-+(DAY(@dt2)-DAY(@dt1)))+%2B+CONVERT(varchar,+'Day(s).')
++++end
++++else+'See+asumption:+@dt1+must+be+>=+@dt2'
end+In_Months_Days|code-block|syntax|javascript|1914484|返回：|1914485|Days+%7C+In_Months_Days

62+++%7C+++2+Month(s)+1Day(s).|1914486|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

See the query below (assumed @dt1 >= @dt2);

<pre><code>Declare @dt1 datetime = '2013-7-3'
Declare @dt2 datetime = '2013-5-2'

select abs(DATEDIFF(DD, @dt2, @dt1)) Days,
case when @dt1 &gt;= @dt2
 then case when DAY(@dt2)&lt;=DAY(@dt1)
 then Convert(varchar, DATEDIFF(MONTH, @dt2, @dt1)) + CONVERT(varchar, ' Month(s) ') + Convert(varchar, DAY(@dt1)-DAY(@dt2)) + CONVERT(varchar, 'Day(s).')
 else Convert(varchar, DATEDIFF(MONTH, @dt2, @dt1)-1) + CONVERT(varchar, ' Month(s) ') + convert(varchar, abs(DATEDIFF(DD, @dt1, DateAdd(Month, -1, @dt1))) - (DAY(@dt2)-DAY(@dt1))) + CONVERT(varchar, 'Day(s).')
 end
 else 'See asumption: @dt1 must be &gt;= @dt2'
end In_Months_Days
</code></pre>

Returns: 

<pre><code>Days | In_Months_Days

62 | 2 Month(s) 1Day(s).
</code></pre>

blocks|key|3446798|text|我发布的解决方案将考虑一个月加30天|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3446799|++select+CONCAT+(CONCAT+(num_months,'+MONTHS+'),+CONCAT+((days-(num_months)*30),'+DAYS+'))
++from+(+
++SELECT+floor(Months_between(To_date('20120325',+'YYYYMMDD'),
+++To_date('20120101',+'YYYYMMDD')))
+++num_months,
+++(+To_date('20120325',+'YYYYMMDD')+-+To_date('20120101',+'YYYYMMDD')+)
+++days
++FROM+++dual);|code-block|syntax|javascript|3446800|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The solution I post will consider a month with 30 days 

<pre><code> select CONCAT (CONCAT (num_months,' MONTHS '), CONCAT ((days-(num_months)*30),' DAYS '))
 from ( 
 SELECT floor(Months_between(To_date('20120325', 'YYYYMMDD'),
 To_date('20120101', 'YYYYMMDD')))
 num_months,
 ( To_date('20120325', 'YYYYMMDD') - To_date('20120101', 'YYYYMMDD') )
 days
 FROM dual);
</code></pre>

blocks|key|1721678|text|这就是你所拥有的吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1721679|select+trunc(months_between(To_date('20120325',+'YYYYMMDD'),to_date('20120101','YYYYMMDD')))+months,
+++++++++++++round(To_date('20120325',+'YYYYMMDD')-add_months(to_date('20120101','YYYYMMDD'),
+++++++++++++++++++++++++++trunc(months_between(To_date('20120325',+'YYYYMMDD'),to_date('20120101','YYYYMMDD')))))+days
++++++++from+dual;|code-block|syntax|javascript|1721680|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

is this what you've ment ?

<pre><code>select trunc(months_between(To_date('20120325', 'YYYYMMDD'),to_date('20120101','YYYYMMDD'))) months,
 round(To_date('20120325', 'YYYYMMDD')-add_months(to_date('20120101','YYYYMMDD'),
 trunc(months_between(To_date('20120325', 'YYYYMMDD'),to_date('20120101','YYYYMMDD'))))) days
 from dual;
</code></pre>

blocks|key|3446872|text|我认为你的问题定义得不够好，原因如下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3446873|依赖于months_between的答案必须处理以下问题:该函数在2013-02-28和2013-03-31之间、2013-01-28和2013-02-28之间以及2013-01-31和2013-02-28之间恰好报告了一个月(我怀疑一些回答者在实践中没有使用这些函数，或者现在必须检查一些生产代码！)|3446874|这是记录在案的行为，在这种行为中，日期都是各自月份的最后一天或落在该月的同一天，则被判断为相隔整数个月。|3446875|因此，当将2013-02-28与2013-01-28或2013-01-31进行比较时，您会得到相同的结果"1“，但将其与2013-01-29或2013-01-30进行比较，结果分别为0.967741935484和0.935483870968+--因此，当一个日期接近另一个日期时，此函数报告的差异可能会增大。|3446876|如果这不是一种可接受的情况，那么你将不得不编写一个更复杂的函数，或者仅仅依赖于假设每月30天(例如)的计算。在后一种情况下，您将如何处理2013-02-28和2013-03-31？|3446877|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I think that your question is not defined well enough, for the following reason.

Answers relying on months_between have to deal with the following issue: that the function reports exactly one month between 2013-02-28 and 2013-03-31, and between 2013-01-28 and 2013-02-28, and between 2013-01-31 and 2013-02-28 (I suspect that some answerers have not used these functions in practice, or are now going to have to review some production code!)

This is documented behaviour, in which dates that are both the last in their respective months or which fall on the same day of the month are judged to be an integer number of months apart.

So, you get the same result of "1" when comparing 2013-02-28 with 2013-01-28 or with 2013-01-31, but comparing it with 2013-01-29 or 2013-01-30 gives 0.967741935484 and 0.935483870968 respectively -- so as one date approaches the other the difference reported by this function can increase.

If this is not an acceptable situation then you'll have to write a more complex function, or just rely on a calculation that assumes 30 (for example) days per month. In the latter case, how will you deal with 2013-02-28 and 2013-03-31?

blocks|key|1914429|text|在Orale+Sql中查找介于两天之间的年-月-日|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1914430|1914431|select+
trunc(trunc(months_between(To_date('20120101',+'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12)+years+,
trunc(months_between(To_date('20120101',+'YYYYMMDD'),to_date('19910228','YYYYMMDD')))+
-
(trunc(trunc(months_between(To_date('20120101',+'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12))*12
months,
+++++++++++++round(To_date('20120101',+'YYYYMMDD')-add_months(to_date('19910228','YYYYMMDD'),
+++++++++++++++++++++++++++trunc(months_between(To_date('20120101',+'YYYYMMDD'),to_date('19910228','YYYYMMDD')))))+days
++++++++from+dual;|code-block|syntax|javascript|1914432|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|K|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|L|8|@]|9|@]|A|$F|G]]|$1|H|3|-4|5|6|7|M|8|@]|9|@]|A|$]]]|I|$]]

Find out Year - Month- Day between two Days in Orale Sql

<hr>

<pre><code>select 
trunc(trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12) years ,
trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD'))) 
-
(trunc(trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD')))/12))*12
months,
 round(To_date('20120101', 'YYYYMMDD')-add_months(to_date('19910228','YYYYMMDD'),
 trunc(months_between(To_date('20120101', 'YYYYMMDD'),to_date('19910228','YYYYMMDD'))))) days
 from dual;
</code></pre>

blocks|key|3359368|text|在这里，我只是比较今天和表中的CREATED_DATE+DATE字段之间的区别，这显然是过去的日期：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3359369|SELECT++
((FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE,+SYSDATE)))+/+12)+*+12)+%7C%7C+'+months,+'++AS+MONTHS,
--+we+take+total+days+-+years(as+days)+-+months(as+days)+to+get+remaining+days
FLOOR((SYSDATE+-+CREATED_DATE)+-++++++--+total+days
(FLOOR((SYSDATE+-+CREATED_DATE)/365)*12)*(365/12)+-++++++--+years,+as+days
--+this+is+total+months+-+years+(as+months),+to+get+number+of+months,+
--+then+multiplied+by+30.416667+to+get+months+as+days+(and+remove+it+from+total+days)
FLOOR(((SYSDATE+-+CREATED_DATE)/365)*12+-+(FLOOR((SYSDATE+-+CREATED_DATE)/365)*12))+*+(365/12))
%7C%7C+'+days+'+AS+DAYS+
FROM+MyTable|code-block|syntax|javascript|3359370|我使用(365/12)或30.416667作为我的转换因子，因为我使用总天数并去掉年份和月份(作为天数)来获得剩余的天数。无论如何，对于我的目的来说，这已经足够好了。|3359371|entityMap^0|F|C|S|4|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Here I'm just doing the difference between today, and a <code>CREATED_DATE</code> <code>DATE</code> field in a table, which obviously is a date in the past:

<pre><code>SELECT 
((FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE))) / 12) * 12) || ' months, ' AS MONTHS,
-- we take total days - years(as days) - months(as days) to get remaining days
FLOOR((SYSDATE - CREATED_DATE) - -- total days
(FLOOR((SYSDATE - CREATED_DATE)/365)*12)*(365/12) - -- years, as days
-- this is total months - years (as months), to get number of months, 
-- then multiplied by 30.416667 to get months as days (and remove it from total days)
FLOOR(((SYSDATE - CREATED_DATE)/365)*12 - (FLOOR((SYSDATE - CREATED_DATE)/365)*12)) * (365/12))
|| ' days ' AS DAYS 
FROM MyTable
</code></pre>

I use (365/12), or 30.416667, as my conversion factor because I'm using total days and removing years and months (as days) to get the remainder number of days. It was good enough for my purposes, anyway.

I need to get the difference between two dates say if the difference is 84 days, I should probably have output as 2 months and 14 days, the code I have just gives the totals. Here is the code

<pre><code>SELECT Months_between(To_date('20120325', 'YYYYMMDD'),
 To_date('20120101', 'YYYYMMDD'))
 num_months,
 ( To_date('20120325', 'YYYYMMDD') - To_date('20120101', 'YYYYMMDD') )
 diff_in_days
FROM dual; 
</code></pre>

Output is:

<pre><code>NUM_MONTHS DIFF_IN_DAYS
2.774193548 84
</code></pre>

I need for example the output for this query to be either 2 months and 14 days at worst, otherwise I won't mind if I can have the exact days after the months figure because those days are not really 14 because all months do not have 30 days.

Get the difference between two dates both In Months and days in sql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要得到两个日期之间的差值，如果差值是84天，我可能会输出2个月14天，我的代码只是给出了总数。以下是代码SELECT Months_between(To_date('20120325', 'YYYYMMDD'),       To_date('20120101', 'YYYYMMDD'))       num_mo...

问在sql中获取以月和日表示的两个日期之间的差异
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在sql中获取以月和日表示的两个日期之间的差异EN