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).
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.
Original Message:
Sent: 06-30-2025 04:21 PM
From: Joanna Schmidt
Subject: MetaData in Expression to Return Date
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_DTELSE %CurrentDateInEnd
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
------------------------------