CODEWITHSUNDEEP
sqloracle-databaseplsql
Salik
Salik

Reputation: 518

How to create day start and end date in PL SQL

What I am trying to do is to create two timestamps a StartDate timestamp which will be 09/08/2015 00:00:00 and an EndDate time stamp which should be 09/08/2015 23:59:59 as easy as it is to achieve in MS SQL, I have not been able to find a Make_Date function or Add_Days function to get either of the timestamps in Oracle PL SQL.

Can anyone help me out?

Upvotes: 3

Views: 29278

Answers (3)

MT0
MT0

Reputation: 167972

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):

SQL Fiddle

Oracle 11g R2 Schema Setup:

Query 1:

SELECT TRUNC( CURRENT_DATE ) AS START_DATE,
       TRUNC( CURRENT_DATE ) + INTERVAL '1' DAY - INTERVAL '1' SECOND AS END_DATE
FROM   DUAL

Results:

|                  START_DATE |                    END_DATE |
|-----------------------------|-----------------------------|
| September, 08 2015 00:00:00 | September, 08 2015 23:59:59 |

Upvotes: 12

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Use TO_DATE to convert string into 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.

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>

Update OP wants the date literal to be dynamic.

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 2 OP wants to know why the time part is hidden in the date.

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 SYSDATE returns two different values. The reason is that the DATE has both datetime elements, what you see depends on the display properties driven by your locale-specific NLS settings.

Use TO_CHAR to convert the date into string to display it in your desired format.

Upvotes: 4

Husqvik
Husqvik

Reputation: 5809

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)

Upvotes: 2

Related Questions