Oracle等同于SQL Server/Sybase DateDiff [英] Oracle equivalent to SQL Server/Sybase DateDiff

问题描述

我们现在正在使用NHibernate连接到基于我们软件安装位置的不同数据库.因此，我正在将许多SQL过程移植到Oracle.

We are now using NHibernate to connect to different database base on where our software is installed. So I am porting many SQL Procedures to Oracle.

SQL Server有一个很好的名为DateDiff的函数，该函数包含日期部分，开始日期和结束日期.

SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate.

日期部分的示例是日，周，月，年等. .

Date parts examples are day, week, month, year, etc. . .

什么是Oracle等效项?

What is the Oracle equivalent?

我还没有找到自己创建的版本吗?

I have not found one do I have to create my own version of it?

(由Mark Harrison更新)，有几个不错的答案可以解释Oracle日期算法.如果您需要Oracle datediff()，请参阅爱因斯坦的答案. (我需要这样做以保持spme SQL脚本在Sybase和Oracle之间兼容.)请注意，此问题同样适用于Sybase.

(update by Mark Harrison) there are several nice answers that explain Oracle date arithmetic. If you need an Oracle datediff() see Einstein's answer. (I need this to keep spme SQL scripts compatible between Sybase and Oracle.) Note that this question applies equally to Sybase.

推荐答案

几年前，我从一篇旧的tom文章中窃取了大部分内容，修复了该文章中的一些错误并进行了清理.在oracle和MSSQL之间，datediff的分界线的计算方式不同，因此您必须小心一些未正确说明MSSQL/Sybase样式边界的示例，这些示例不能提供分数结果.

I stole most of this from an old tom article a few years ago, fixed some bugs from the article and cleaned it up. The demarcation lines for datediff are calculated differently between oracle and MSSQL so you have to be careful with some examples floating around out there that don't properly account for MSSQL/Sybase style boundaries which do not provide fractional results.

使用以下代码，您应该能够使用MSSQL语法并获得与MSSQL相同的结果，例如SELECT DATEDIFF(dd，getdate()，DATEADD(dd，5，getdate()))FROM DUAL;

With the following you should be able to use MSSQL syntax and get the same results as MSSQL such as SELECT DATEDIFF(dd,getdate(),DATEADD(dd,5,getdate())) FROM DUAL;

我只声称它有效-并不是说它有效或最好的方法.我不是Oracle专家:)您可能需要三思而后行，以使用我的函数宏来解决需要在dd，mm，hh，mi..etc等周围加引号的问题.

I claim only that it works - not that its effecient or the best way to do it. I'm not an Oracle person :) And you might want to think twice about using my function macros to workaround needing quotes around dd,mm,hh,mi..etc.

(由Mark Harrison更新)添加了dy函数作为dd的别名.

(update by Mark Harrison) added dy function as alias for dd.

CREATE OR REPLACE FUNCTION GetDate 
RETURN date IS today date;
BEGIN
RETURN(sysdate);
END;
/

CREATE OR REPLACE FUNCTION mm RETURN VARCHAR2 IS BEGIN RETURN('mm'); END;
/
CREATE OR REPLACE FUNCTION yy RETURN VARCHAR2 IS BEGIN RETURN('yyyy'); END;
/
CREATE OR REPLACE FUNCTION dd RETURN VARCHAR2 IS BEGIN RETURN('dd'); END;
/
CREATE OR REPLACE FUNCTION dy RETURN VARCHAR2 IS BEGIN RETURN('dd'); END;
/
CREATE OR REPLACE FUNCTION hh RETURN VARCHAR2 IS BEGIN RETURN('hh'); END;
/
CREATE OR REPLACE FUNCTION mi RETURN VARCHAR2 IS BEGIN RETURN('mi'); END;
/
CREATE OR REPLACE FUNCTION ss RETURN VARCHAR2 IS BEGIN RETURN('ss'); END;
/

CREATE OR REPLACE Function DateAdd(date_type IN varchar2, offset IN integer, date_in IN date )
RETURN date IS date_returned date;
BEGIN
date_returned := CASE date_type
    WHEN 'mm'   THEN add_months(date_in,TRUNC(offset))
    WHEN 'yyyy' THEN add_months(date_in,TRUNC(offset) * 12)
    WHEN 'dd'   THEN date_in + TRUNC(offset)
    WHEN 'hh'   THEN date_in + (TRUNC(offset) / 24)
    WHEN 'mi'   THEN date_in + (TRUNC(offset) /24/60)
    WHEN 'ss'   THEN date_in + (TRUNC(offset) /24/60/60)
    END;
RETURN(date_returned);
END;
/

CREATE OR REPLACE Function DateDiff( return_type IN varchar2, date_1 IN date, date_2 IN date)
RETURN integer IS number_return integer;
BEGIN
number_return := CASE return_type
    WHEN 'mm'   THEN ROUND(MONTHS_BETWEEN(TRUNC(date_2,'MM'),TRUNC(date_1, 'MM')))
    WHEN 'yyyy' THEN ROUND(MONTHS_BETWEEN(TRUNC(date_2,'YYYY'), TRUNC(date_1, 'YYYY')))/12
    WHEN 'dd'   THEN ROUND((TRUNC(date_2,'DD') - TRUNC(date_1, 'DD')))
    WHEN 'hh'   THEN (TRUNC(date_2,'HH') - TRUNC(date_1,'HH')) * 24
    WHEN 'mi'   THEN (TRUNC(date_2,'MI') - TRUNC(date_1,'MI')) * 24 * 60
    WHEN 'ss'   THEN (date_2 - date_1) * 24 * 60 * 60
    END;
RETURN(number_return);
END;
/

这篇关于Oracle等同于SQL Server/Sybase DateDiff的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！