2个日期之间的天数差异Oracle SQL [英] Difference in days between 2 dates Oracle SQL

问题描述

好吧，所以我检查了stackoverflow上的许多其他文章，看看是否在任何地方都提到了这个问题，提供的答案对我来说意义不大...我认为他们在谈论什么完全不同.

Alright, so I've checked many many many other posts on stackoverflow to see if this is mentioned anywhere, and the answers provided don't quite make sense to me...I'm thinking they're talking about something completely different.

这是我要使用Oracle SQL Developer进行的操作:

Here's what I want to do, using Oracle SQL Developer:

-从purch_date检索条目

-Retrieve entries from purch_date

-获取当前年份的购买日期和圣诞节之间的天数差异

-Get the difference IN DAYS between the purch_date and Christmas of the CURRENT YEAR

* *因此，不能在其中对'2012'进行硬编码.我需要检索它.

**So, therefore '2012' can't be hard-coded in there. I need to retrieve it.

以下是在MySQL中100％有效的查询:

Here's the query that works 100% in MySQL:

SELECT purch_id AS PURCH_ID, 
DATEDIFF(CONCAT(YEAR(NOW()),'-12-25'), purch_date) AS DAYS_TO_CHRISTMAS
FROM CS260USER.candy_purchase;

非常，我需要它才能在Oracle中工作.

Pretty much, I need that to work in Oracle.

有人可以帮我吗?

非常感谢！

-马修

推荐答案

假定purch_date被定义为DATE

SELECT purch_id,
       (trunc(sysdate, 'YYYY') +
         interval '11' month +
         interval '24' day) -
        purch_date days_to_christmas
  FROM CS260USER.candy_purchase;

一些笔记

1. 如果purch_date具有时间成分，则可能要截断日期减法的结果.
2. 如果此查询在12月26日至12月31日之间执行，则您仍将查看今年的圣诞节(即过去的日期)，而不是明年的圣诞节.我想如果purch_date是例如12月30日，那么您希望结果是到下一个圣诞节的360天，而不是到最后一个圣诞节的-5天.

1. If purch_date has a time component, you might want to truncate the result of the date subtraction.
2. If this query is executed between December 26 and December 31, you'll still be looking at this year's Chrismas (i.e. a date in the past) rather than next year's Christmas. I would thing that if purch_date was, for example, December 30, that you would want the result to be 360 days until the next Christmas rather than -5 days until the last Christmas.

这篇关于2个日期之间的天数差异Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！