blocks|key|944731|text|使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|944732|TO_DAYS(date2)+-+To_DAYS(date1)|code-block|syntax|javascript|944733|它返回date1和date2之间的天数。然后，您可以对结果执行所需的操作。|944734|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Use 

<pre><code>TO_DAYS(date2) - To_DAYS(date1)
</code></pre>

It returns the number of days between date1 and date2. Then you can do with the result what you need.

blocks|key|944743|text|为了得到与Excel相同的结果，我在MySQL中找到了以下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|944744|++++select+case+
when+(day(Startdate)>=30+or+Startdate=last_day(Startdate)+then
++++case
++++when(day(Enddate)>=30)+then
++++++++30*(12*(year(Enddate)-year(Startdate))%2Bmonth(Enddate)-month(Startdate))
++++else
++++++++30*(12*(year(Enddate)-year(Startdate))%2Bmonth(Enddate)-month(Startdate))%2Bdays(Enddate)-30
++++end
else
++++30*(12*(year(Enddate)-year(Startdate))%2Bmonth(Enddate)-month(Startdate))%2Bdays(Enddate)-days(Startdate)
end|code-block|syntax|javascript|944745|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|$]]

to get the same result like Excel, I found the following code to use in MySQL:

<pre><code> select case 
when (day(Startdate)&gt;=30 or Startdate=last_day(Startdate) then
 case
 when(day(Enddate)&gt;=30) then
 30*(12*(year(Enddate)-year(Startdate))+month(Enddate)-month(Startdate))
 else
 30*(12*(year(Enddate)-year(Startdate))+month(Enddate)-month(Startdate))+days(Enddate)-30
 end
else
 30*(12*(year(Enddate)-year(Startdate))+month(Enddate)-month(Startdate))+days(Enddate)-days(Startdate)
end
</code></pre>

blocks|key|3002296|text|日期=起始日期编辑=终止日期|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3002297|计算两个日期之间的天数，考虑到一个月是30天，一年是360天，并检查日期是否在月底，例如从1.1.2019到28.2.2019+=+60天等。|3002298|对于1个月或1年，更新日期应在前一天，因此1.1+-+31.1+=+30天，1.1+-+1.2+=+31天|3002299|3002300|+SELECT最大(+0，(年(DATE_ADD(编辑，间隔1天))-年(日期))*+360+%2B(月(DATE_ADD(编辑，间隔1天))-月(日期))*+30+%2B(IF(日期)(DATE_ADD(编辑，间隔1天)=DAYOFMONTH(LAST_DAY(DATE_ADD(编辑，间隔1天))，30，DAYOFMONTH(DATE_ADD(edate，间隔1天))-IF(DAYOFMONTH(日期)=DAYOFMONTH(LAST_DAY(日期))，30，DAYOFMONTH(日期)+)|blockquote|3002301|3002302|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|Q|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|L|$]]

sdate = from date
edate = to date

this calculate the days between the 2 date, taking into account that a month is 30 days, 1 year is 360 days, and checking if the date are at the end of the month, so e.g. from 1.1.2019 to 28.2.2019 = 60 days etc.

to be 1 month or 1 year, the edate should be a day before, so 1.1 - 31.1 = 30 days, 1.1 - 1.2 = 31 days

<blockquote>
 SELECT GREATEST(
 0,
 (YEAR(DATE_ADD(edate, INTERVAL 1 DAY)) - YEAR(sdate)) * 360 +
 (MONTH(DATE_ADD(edate, INTERVAL 1 DAY)) - MONTH(sdate)) * 30 +
 (
 IF(DAYOFMONTH(DATE_ADD(edate, INTERVAL 1 DAY)) = DAYOFMONTH(LAST_DAY(DATE_ADD(edate, INTERVAL 1 DAY))), 30, DAYOFMONTH(DATE_ADD(edate, INTERVAL 1 DAY))) -
 IF(DAYOFMONTH(sdate) = DAYOFMONTH(LAST_DAY(sdate)), 30, DAYOFMONTH(sdate))
 )
</blockquote>

blocks|key|3002315|text|在这里已经很晚了，但是发布我的解决方案以供将来参考|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3002316|CREATE+FUNCTION+`DAYS360_UDF`(sdate+DATE,+edate+DATE)
RETURNS+INTEGER
DETERMINISTIC
CONTAINS+SQL
BEGIN
++++DECLARE+sdate_360+INTEGER;
++++DECLARE+edate_360+INTEGER;
++++SET+sdate_360+=+(+YEAR(sdate)+*+360+)+%2B+(+(MONTH(sdate)-1)*30+)+%2B+DAY(sdate);
++++SET+edate_360+=+(+YEAR(edate)+*+360+)+%2B+(+(MONTH(edate)-1)*30+)+%2B+DAY(edate);
++++RETURN+edate_360+-+sdate_360;
END|code-block|syntax|javascript|3002317|用法-+select+DAYS360_UDF('20130908',+'20140908')|offset|length|style|CODE|3002318|entityMap^0|0|0|4|16|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Pretty late here, but posting my solution for future reference
<pre><code>CREATE FUNCTION `DAYS360_UDF`(sdate DATE, edate DATE)
RETURNS INTEGER
DETERMINISTIC
CONTAINS SQL
BEGIN
 DECLARE sdate_360 INTEGER;
 DECLARE edate_360 INTEGER;
 SET sdate_360 = ( YEAR(sdate) * 360 ) + ( (MONTH(sdate)-1)*30 ) + DAY(sdate);
 SET edate_360 = ( YEAR(edate) * 360 ) + ( (MONTH(edate)-1)*30 ) + DAY(edate);
 RETURN edate_360 - sdate_360;
END
</code></pre>
Usage -
<code>select DAYS360_UDF('20130908', '20140908')</code>

I working on a financial project where i need to calculate the difference of arrear days. If I use the method datediff() of mysql then it returns the result based on 365 days. I need the result based on 360 days. if use the following sql query

<pre><code>select datediff('20140908','20130908') from dual;
</code></pre>

mysql returns the date difference 365. This is the actual date difference but in accounting/Financial calculation the difference is exactly one year (360 days). This is what I want. The result should be 360 instead 365.
Currently I want to use US standard.

Date difference in mysql based on 360 days

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

我在一个金融项目中工作，我需要计算欠款天数的差额。如果我使用mysql的datediff()方法，那么它将返回基于365天的结果。我需要360天的结果。如果使用以下sql查询select datediff('20140908','20130908') from dual;mysql返回日期差异365。这是实际的日期差异...

问mysql中基于360天的日期差异
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql中基于360天的日期差异EN