CODEWITHSUNDEEP
sqloracle-database
missspeedy23
missspeedy23

Reputation: 11

Last day of month joined to second table

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!

Upvotes: 0

Views: 545

Answers (2)

MT0
MT0

Reputation: 168671

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

Upvotes: 1

NikNik
NikNik

Reputation: 2301

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.

Upvotes: 0

Related Questions