1

I have a table:

Table1(DDate date,Deposit_Key Number , Amount Number)

some example data are:

DDate       Deposit_key        Amount
1/1/2019          1              10
1/2/2019          1              20
1/3/2019          1              30
1/4/2019          1              40
  ----           ---             ---
1/31/2019         1              100

I have data for one month and I need to retrieve the Amount for the last day of the month . I mean I need to have 100 . What is the best query possible for this? I'm new to oracle and I want to know whether there are better ways than using row_number() over(partition)? Thanks in advance

6
  • 1
    Why do you think you need a stored procedure?
    – user1822
    Oct 7, 2019 at 14:35
  • 1
    I do no need a stored procedure. I just need to retrieve data for the last day of each month @a_horse_with_no_name
    – Pantea
    Oct 8, 2019 at 5:24
  • 1
    Then you don't need PL/SQL
    – user1822
    Oct 8, 2019 at 5:33
  • 1
    OK . So what is the best way? @a_horse_with_no_name
    – Pantea
    Oct 8, 2019 at 5:39
  • 1
    You only need plain SQL, not PL/SQL - PL/SQL is only used to write stored procedures, functions and triggers.
    – user1822
    Oct 8, 2019 at 5:42

1 Answer 1

1

The Oracle built in function called LAST_DAY can help you here.

It can be used with dates or timestamps and an example based on your table description is:

SELECT * FROM TABLE1 
WHERE EXTRACT(DAY FROM DDATE) = EXTRACT(DAY FROM LAST_DAY(DDATE))
AND EXTRACT (MONTH FROM DDATE) = 1;

There are many ways to work with dates in Oracle but I like using EXTRACT as it works with dates and timestamps and means you don't have to use the TO_CHAR function.

1
  • simpler would be where trunc(ddate) = last_day(trunc(ddate)). Maybe without the truncs if the OPs data has the time parts of each date set to midnight.
    – Boneist
    Oct 8, 2019 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.