PS Query & SQL

I am working on a query to provide a calculated due date.  I have gotten all my dates to return in their columns, but the Expression to return the ultimate a single date is escaping me!  

To calculate the "due date" as 10 days prior to the TERM_BEGIN_DT (use field as CALC_DUE_DT in Expression):  %DateAdd(%datein(A.TERM_BEGIN_DT), -10)

To get today's date (one of many ways, again in Expression): %CurrentDateIn

SQL of query working thus far:

SELECT DISTINCT A.INSTITUTION, A.ACAD_CAREER, A.STRM, TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD'), ((TO_DATE(TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD'),'YYYY-MM-DD')) + ( -10)), TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
  FROM PS_TERM_TBL A
  WHERE ( A.INSTITUTION = :1
     AND A.ACAD_CAREER = :2
     AND A.STRM = :3)
  GROUP BY  A.INSTITUTION,  A.ACAD_CAREER,  A.STRM,  A.TERM_BEGIN_DT,  TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')

  

  

Next Step - do the comparison and return a single date!

Basically I want to end up with this as a column for end use:

IF %CurrentDateIn > CALC_DUE_DT THEN CALC_DUE_DT
ELSE %CurrentDateIn
End

I am having fits with using the Expression when most of what I find is designed to be used in AppDesigner as pure SQL.  This leads to %DateIn vs. %DateOut for Select vs. From portions of SQL.  That doesn't help a whole lot in Expression!  I have tried all the combinations I could think of including reusing the TO_DATE(TO_CHAR)... stuff - no dice.  I either get "the FROM is in an unexpected place" or some other less useful error.  

The most annoying thing is I can use these Expressions as straight-up criteria and it works, but only to show everything or a null:

SELECT A.INSTITUTION, A.ACAD_CAREER, A.STRM, TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD'), ((TO_DATE(TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD'),'YYYY-MM-DD')) + ( -10)), TRUNC(SYSDATE)
  FROM PS_TERM_TBL A
  WHERE ( A.INSTITUTION = :1
     AND A.ACAD_CAREER = :2
     AND A.STRM = :3)
  GROUP BY  A.INSTITUTION,  A.ACAD_CAREER,  A.STRM,  A.TERM_BEGIN_DT,  TRUNC(SYSDATE)
  HAVING ( ((TO_DATE(A.TERM_BEGIN_DT,'YYYY-MM-DD')) + ( -10)) <= TRUNC(SYSDATE))

  

Would someone with more experience/patience on Dates be able to help get me going on this Expression?  Much appreciation for any walk through of how I'm not getting the proper exit off of this freeway!

------------------------------
Joanna Schmidt
Sr. Business Analyst - Financial Aid
Maricopa Community College System Office
joanna.schmidt@domail.maricopa.edu
------------------------------

Hello Joanna,

You aren't the first person to be on the PSQuery Date Highway only to end up careening off the exit ramp into a ditch marked "Expression Editor is Not SQL."  I have learned this the hard way as well. Hopefully the following can work for you.

So far CALC_DUE_DATE (or whatever you name that field) this expression should work assuming you are wanting to continue using meta-SQL:

%DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)

For the next expression, FINAL_DUE_DATE (or whatever you name that field), the following should work:

CASE WHEN %CurrentDateIn > %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
     THEN %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
     ELSE %CurrentDateIn
END

Let me know if this works.

P.S.  If you  want to format the date, you could use the following:

TO_CHAR(
  CASE WHEN %CurrentDateIn > %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
       THEN %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
       ELSE %CurrentDateIn
  END,
  'MM/DD/YYYY'
)

------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada-Las Vegas
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Would you believe I tried this same Expression? 

CASE
WHERE ((%CurrentDateIn) > (%DateAdd(%datein(A.TERM_BEGIN_DT), -10))) THEN %DateOut(%DateAdd(%datein(A.TERM_BEGIN_DT), -10))
ELSE %CurrentDateIn
END

Oh, but I said WHERE...not WHEN - that is what comes from reading SQL based info!  No wonder Query was expecting the FROM somewhere else (smh).  

Yes, Dan's SQL works - as does Tim's.  Of other note, my alligators are eating the wrong part of that statement for what I really need (dates, dates, dates, give me a fig newton already).  Given today is June 30 and the "10 days prior" was back in May, I really should return the June date as it's due today (well midnight, but I'm not going to quibble about is it a coach or a pumpkin - get home/pay before curfew or face the dropped class).  

  
It's like asking your co-pilot "Do I turn here?" and they say "Yes, right here!" when "right here" is not RIGHT but LEFT...so I was left in an endless circle from my own syntax error where I ended up nowhere!   Thank you both for the assists!

------------------------------
Joanna Schmidt
Sr. Business Analyst - Financial Aid
Maricopa Community College System Office
joanna.schmidt@domail.maricopa.edu
------------------------------

Original Message:
Sent: 06-30-2025 05:32 PM
From: Daniel Labrecque
Subject: MetaData in Expression to Return Date

Hello Joanna,

You aren't the first person to be on the PSQuery Date Highway only to end up careening off the exit ramp into a ditch marked "Expression Editor is Not SQL."  I have learned this the hard way as well. Hopefully the following can work for you.

So far CALC_DUE_DATE (or whatever you name that field) this expression should work assuming you are wanting to continue using meta-SQL:

%DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)

For the next expression, FINAL_DUE_DATE (or whatever you name that field), the following should work:

CASE WHEN %CurrentDateIn > %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
     THEN %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
     ELSE %CurrentDateIn
END

Let me know if this works.

P.S.  If you  want to format the date, you could use the following:

TO_CHAR(
  CASE WHEN %CurrentDateIn > %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
       THEN %DateAdd(%DateIn(A.TERM_BEGIN_DT), -10)
       ELSE %CurrentDateIn
  END,
  'MM/DD/YYYY'
)

------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada-Las Vegas
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Hi Joanna,

I think I ran into the same problems you're describing when trying to recreate this in Query Manager. As we all know, this tool automatically casts dates as CHAR, and that automatically-inserted logic is messing us up. For example, when I write this in an expression (which would totally work as raw SQL):

It produces this invalid SQL:

TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD') - 10

If I instead write this expression:

It produces this SQL:

TO_DATE( TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD')) - 10

...which produces valid results as below.

I'm then able to extend that to what I think is the conditional logic you're looking for.

...which gets rewritten as:

CASE WHEN
SYSDATE > TO_DATE( TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD')) - 10 THEN TO_DATE( TO_CHAR(A.TERM_BEGIN_DT,'YYYY-MM-DD')) - 10
ELSE SYSDATE END

...which produces results as below.

------------------------------
Timothy Krug
Consulting Manager
Huron Consulting Group
------------------------------