Current Position:Home > Need Help for getting today's records from sysdate with current time.

Need Help for getting today's records from sysdate with current time.

Update:11-30Source: network consolidation
Advertisement
Here is the procedure which gives me the below mentioned output.
Create or Replace Procedure pt(rdate date default sysdate) is
sdate date;
edate date;
begin
sdate:=trunc(NVL(rdate,sysdate));
edate:=NVL(trunc(rdate)+23/24/+59/24/60+59/24/60/60,sysdate);
dbms_output.put_line(sdate || ' ' || edate);
end;
Scenerio 1 (This is ok and is giving the desired output)
In the above procedure if i give any date as parameter then it will take the input date as the parameter and execute accordingly and gives the result like SQL> EXEC PT('02-JAN-07');
SQL> 02-02-07 00:00:00 02-02-07 23:59:59
Scenerio 2 (This is giving problem it is not giving the desired output).
In the above procedure if i dont give any date as parameter then it will take the sysdate as the parameter and execute accordingly and gives the result like SQL> EXEC PT();
SQL> 25-10-07 00:00:00 25-10-07 23:59:59
INSTEAD OF THIS OUTPUT FOR NOT GIVING ANY DATE PARAMETER I SHOULD GET BELOW O/P
SQL> 25-10-07 00:00:00 25-10-07 12:28:43 THE CURRENT DATE AND TIME
AND NOT 25-10-07 23:59:59.
Here i think the NVL function is not working properly or i am doing something incorrect, Cna anyone guide me.
Thnx

The Best Answer

Advertisement
Scenerio 1 (This is ok and is giving the desired output)
... and gives the result like SQL> EXEC PT('02-JAN-07');
SQL> 02-02-07 00:00:00 02-02-07 23:59:59
02-JAN should be 02-01
This?
SQL> declare
   rdate date := sysdate-5;
   sdate date;
   edate date;
begin
   select trunc(nvl(rdate, sysdate)),
      trunc(nvl(rdate, sysdate))+1-(1/24/60/60)
   into sdate, edate
   from dual;
   dbms_output.put_line(to_char(sdate,'YYYY/MM/DD HH24:MI:SS') || ' ' || to_char(edate,'YYYY/MM/DD HH24:MI:SS'));
end;
2007/10/20 00:00:00 2007/10/20 23:59:59
PL/SQL procedure successfully completed.
  • Need Help for getting today's records from sysdate with current time. Update:11-30

    Here is the procedure which gives me the below mentioned output. Create or Replace Procedure pt(rdate date default sysdate) is sdate date; edate date; begin sdate:=trunc(NVL(rdate,sysdate)); edate:=NVL(trunc(rdate)+23/24/+59/24/60+59/24/60/60,sysdate

  • Need  trigger when update one column then record sysdate on another collumn Update:11-30

    Hi Gurus I have a table called RTV_PLAN And it have two columns, FORCE_KEYIN_FLAG VARCHAR(2) FORCE_KEYIN_DATE DATE I want when FORCE_KEYIN_FLAG get a value Then FORCE_KEYIN_DATE record the sysdate Please kindly give some help Many thanks savenHi Just

  • The same Object Version Number for the same person id multiple times Update:10-11

    Hello all, I am currently facing an issue with HRMS tables and the object version number for employees. I am trying to write a report but due to the same object version number for the same person appearing in several row i am getting duplicate inform

  • Problem in Date Update:10-11

    Hello, I have table with one date coloumn having rows 2000 rows. Out of 2000 rows, arround 1550 rows are having data 09/04/2002. other rows are having 05/04/2002. Problem is : If I run a following i gets 0 rows selected. SELECT COUNT(*) FROM TABLENAM

  • Please review procedure for performance tuning Update:11-30

    Hi Friends, I have a procedure, which is taking lot of time for execution. Please help me how can i tune this procedure to reduce the execution time. Purpose of the procedure : This procedure runs every data to load data from DWH to Data Marts, We ha

  • Max (date) in sub-query Update:11-30

    I have an issue with a big view and several queries running on it where the max (date) is required in the sub-select (example below). When I run traces and explain, the perf problem is when the where cluase is evaluated, the view is pulled second tim

  • Quering with sysdate function Update:11-30

    hi all , there is a strange thing right here , i set the where clause for my database block to , ( test_date = sysdate ) then , query caused no records to be retrieved , and i am sure that the table has records with sysdate date . why does this happe

  • Can I use SYSDATE for retrieving data? Update:11-30

    Hi all, I'm working on MS Windows XP SP3 and my date format in oracle is '01-DEC-2008'. Now I have created a table with DATE type column then I insert a record in it using the SYSDATE like this INSERT INTO Birthdays (ID, Name, BDate) VALUES (1, 'Zika

  • Correct my query. Update:11-30

    Hi gurus, I am in need of correction of my query to get the correct output. The table structure and data is as follows: create table sample_pid ( pid char(1), asof date, edf number select * from table sample_pid;      PID     ASOF     EDF      A     

  • Filtering Interactive Reports with Greater Than Less Than Operators Update:11-30

    The available filter operators in the interactive reports do not include greater than or less than. How is it possible to enable an application user to filter records where greater than or less then logic is needed? For example the user wants to quer