Search code examples
sqloracle-databasepeoplesoft

Concatenating and comparing values of fields as date

Can you help me out with this problem.

I have the following fields below with sample values:

STRM = 1171

TERM_BEGIN_DT = 01-SEPT-18

TERM_END_DT = 31-JUL-19

ACAD_YEAR = 2018

*Additional Info: 2018/19 Academic Year

1st August 2018 – 31st July 2019*

What I want to do is, I want to get the STRM within the current ACADEMIC YEAR

The SQL i want is:

SELECT STRM FROM PS_TERM_TBL
WHERE TERM_BEGIN_DT BETWEEN '01-AUG-18' AND '31-JUL-19';

The problem is, given only the values above, I have to hard code '01-AUG-' and '31-JUL-' and concatenate each with the '18' and '18' + 1 of ACAD_YEAR respectively.

  1. Main question is, how do i do this?
  2. How do i get the 18 of 2018 in ACAD_YEAR and then add 1 to it to get 19?
  3. I think i will get a invalid type error with this one, so what do i have to convert to to_date in order for the comparison to be legit?
Solution

  • You can use ADD_MONTHS to translate the epoch date of the start of the current academic year back to the start of the calendar year, TRUNCate the value to the beginning of the year, and then use ADD_MONTHS again to reverse the initial translation:

    SELECT STRM
FROM   PS_TERM_TBL
WHERE  TERM_BEGIN_DT >= ADD_MONTHS( TRUNC( ADD_MONTHS( SYSDATE, -7 ), 'YYYY' ),  7 )
AND    TERM_BEGIN_DT  < ADD_MONTHS( TRUNC( ADD_MONTHS( SYSDATE, -7 ), 'YYYY' ), 19 )

    As an aside, '01-AUG-18' is not a date - it is a string literal that Oracle is implicitly converting to a date using the NLS_DATE_FORMAT session parameter (which is something that can change depending on the user's territory and users can also change their own session's settings so should not be relied upon to be give a consistent format model). If you want to specify dates then you should use either:

    • a date literal DATE '2018-08-01'; or
    • an explicit conversion from a string literal TO_DATE( '01-AUG-18', 'DD-MON-RR', 'NLS_DATE_LANGUAGE = American' )