oracle 如何在 PL SQL 中创建日期开始和结束日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32454584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create day start and end date in PL SQL
提问by Salik
What I am trying to do is to create two timestamps a StartDatetimestamp which will be 09/08/2015 00:00:00and an EndDatetime stamp which should be 09/08/2015 23:59:59as easy as it is to achieve in MS SQL, I have not been able to find a Make_Datefunction or Add_Daysfunction to get either of the timestamps in Oracle PL SQL.
我所要做的是创建两个时间戳一个StartDate时间戳，这将是09/08/2015 00:00:00和EndDate这应该是时间戳09/08/2015 23:59:59容易，因为它是在MS SQL实现，我一直没能找到一个Make_Date函数或Add_Days函数来获取任一Oracle PL SQL 中的时间戳。
Can anyone help me out?
谁能帮我吗？
回答by MT0
Rather than using fractional numbers 86399 / 86400(which requires some working out when reviewing the code to see why you picked those magic numbers) to get the end date you can explicitly state the time periods using INTERVALS(which is easy to see at a glance what you are doing):
而不是使用小数86399 / 86400（这需要在查看代码时进行一些工作以了解您为什么选择这些幻数）来获得结束日期，您可以明确说明使用的时间段INTERVALS（这很容易一目了然地看到您在做什么)：
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
Query 1:
查询 1：
SELECT TRUNC( CURRENT_DATE ) AS START_DATE,
TRUNC( CURRENT_DATE ) + INTERVAL '1' DAY - INTERVAL '1' SECOND AS END_DATE
FROM DUAL
结果：
| START_DATE | END_DATE |
|-----------------------------|-----------------------------|
| September, 08 2015 00:00:00 | September, 08 2015 23:59:59 |
回答by Lalit Kumar B
Use TO_DATEto convert stringinto DATE.
使用TO_DATE将字符串转换为DATE。
SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';
Session altered.
SQL> SELECT to_date('09/08/2015 00:00:00' ,'mm/dd/yyyy hh24:mi:ss') start_date,
2 to_date('09/08/2015 23:59:59' ,'mm/dd/yyyy hh24:mi:ss') end_date
3 FROM dual;
START_DATE END_DATE
------------------- -------------------
09/08/2015 00:00:00 09/08/2015 23:59:59
SQL>
You could also use the ANSI TIMESTAMP Literal.
您还可以使用ANSI TIMESTAMP 文字。
SQL> SELECT TIMESTAMP '2015-08-09 00:00:00' start_date,
2 TIMESTAMP '2015-08-09 23:59:59' end_date
3 FROM dual;
START_DATE END_DATE
---------------------------- -------------------------------
09-AUG-15 12.00.00.000000000 09-AUG-15 11.59.59.000000000 PM
SQL>
UpdateOP wants the date literal to be dynamic.
更新OP 希望日期文字是动态的。
SQL> SELECT TRUNC(SYSDATE) start_date,
2 TRUNC(SYSDATE) + 86399 / 86400 end_date
3 FROM dual;
START_DATE END_DATE
------------------- -------------------
09/08/2015 00:00:00 09/08/2015 23:59:59
Update 2OP wants to know why the time part is hidden in the date.
更新 2OP 想知道为什么时间部分隐藏在日期中。
SQL> alter session set nls_date_format='mm/dd/yyyy';
Session altered.
SQL> SELECT sysdate FROM DUAL;
SYSDATE
----------
09/08/2015
SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';
Session altered.
SQL> SELECT sysdate FROM DUAL;
SYSDATE
-------------------
09/08/2015 15:46:14
So, what happened above? The same SYSDATEreturns two different values. The reason is that the DATEhas both datetimeelements, what you see depends on the display properties driven by your locale-specific NLS settings.
那么，上面发生了什么？同一个SYSDATE返回两个不同的值。原因是DATE有两个日期时间元素，您看到的取决于您的区域设置特定 NLS 设置驱动的显示属性。
Use TO_CHARto convert the date into string to display it in your desired format.
使用TO_CHAR将日期转换为字符串，以您想要的格式显示它。
回答by Husqvik
Using values from table:
使用表中的值：
SELECT
DATE_VALUE,
TRUNC(DATE_VALUE) START_DATE,
TRUNC(DATE_VALUE) + 86399 / 86400 END_DATE
FROM
(SELECT SYSDATE - LEVEL + 1 DATE_VALUE FROM DUAL CONNECT BY LEVEL <= 10)
