0

I need to get the last day of the previous month and then join this to another table to return the year/month column that the date relates to but I'm struggling to achieve what I want.

I have tried:

SELECT b.yrmonth, LAST_DAY(ADD_MONTHS(SYSDATE,-1)) DT 
FROM dual a
INNER JOIN D_DAY b on DT = b.DT

The year month just returns everything in the table rather than just one row so any help would be much appreciated!

missspeedy23
  • 11
  • 2
  • Possible duplicate of [Getting Last Day of Previous Month in Oracle Function](https://stackoverflow.com/questions/4957224/getting-last-day-of-previous-month-in-oracle-function) –  Oct 09 '17 at 09:11
  • @a_horse_with_no_name Not a duplicate. The OP knows how to get the last day of the previous month; the issue is with the join condition. – MT0 Oct 09 '17 at 09:30
  • An [mcve](https://stackoverflow.com/help/mcve) would be helpful. – William Robertson Oct 09 '17 at 10:12

2 Answers2

1

Your query is effectively:

SELECT b.yrmonth,
       'some constant masking b.DT' DT 
FROM   dual a
       INNER JOIN
       D_DAY b
       on ( b.DT = b.DT ) -- Always true

You do not need to join the DUAL table and need to filter your table in the WHERE clause.

If the DT date column has varying time components:

SELECT yrmonth, dt
FROM   D_DAY
WHERE  DT >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))
AND    DT <  TRUNC(SYSDATE,'MM');

(Which will allow the database to use indexes on the DT column)

or, if your DT column always has dates with the time component at midnight:

SELECT yrmonth, dt
FROM   D_DAY
WHERE  DT = TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)));
MT0
  • 143,790
  • 11
  • 59
  • 117
0

You don't need to join with dual table. You can simply add your condition in thewhere clause:

select *
from D_DAY b
where TRUNC(b.DT) = TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))

and cast the date (with time) as date because LAST_DAY function returns date (with time component and if you want to check the date, you need to cast it before.

NikNik
  • 2,191
  • 2
  • 15
  • 34
  • You do not need to use `TO_DATE` on `DATE` data types. `LAST_DAY()` returns a `DATE` data type and `TO_DATE` takes a string input so Oracle has to do an implict `TO_CHAR` and is effectively doing `TO_DATE( TO_CHAR( LAST_DAY( ... ), NLS_DATE_FORMAT ), 'DD-MON-YYYY' )`. This will fail if the user changes the `NLS_DATE_FORMAT` session parameter so that it does not match the explicit format model (or if the query is run internationally when the default `NLS_DATE_FORMAT` is likely to change between countries). – MT0 Oct 09 '17 at 09:26
  • I added the cast because LAST_DAY() is returning a TIMESTAMP data type; if you try to check it, this returns false: `LAST_DAY(ADD_MONTHS(SYSDATE,-1)) = to_date('30/09/2017', 'dd/MM/YYYY')` – NikNik Oct 09 '17 at 09:33
  • It is not returning a timestamp - it is returning a date with a time component (the Oracle `DATE` data type has year/month/day/hour/minute/second components). If the time component is not required then use `TRUNC` to truncate it back to midnight. Using `TO_DATE` and relying on an implicit `TO_CHAR` is the wrong solution. – MT0 Oct 09 '17 at 09:41
  • Ok, I agree that TRUNC is better, but still you need to cast the date (with time) :) however thank you for your explanation. – NikNik Oct 09 '17 at 09:46
  • `'DD-MON-YYY'` isn't a valid format model for `trunc(datetime)`. It needs to be one of these: http://docs.oracle.com/database/121/SQLRF/functions271.htm – William Robertson Oct 09 '17 at 09:54
  • Ok, I forgot to remove it. Thank you @WilliamRobertson – NikNik Oct 09 '17 at 09:55