PeopleSoft Query and date prompts

Question

I have a PS Query and one of its prompts is ADM_CREATION_DT where the condition is:

This is a FROM date. All records starting from the selected date to the current date will be included in the report. Leaving this blank will default this to the current date.

I'm quite lost how to put this in the criteria.

part where Invalid datatype error happens

Answer 1

I think you can make a decode expression and make the date sysdate if the prompt value is empty.

decode(:1,NULL,sysdate,:1)

here are some search results for: ps query decode prompt sysdate

• https://peoplesoftexperts.blogspot.com/2007/12/how-to-default-sysdate-current-date-for.html

• https://it.toolbox.com/question/optional-prompt-for-date-in-ps-query-082912

• http://www.psoftsearch.com/optional-prompting-ps-query/

Looking at the search results above, you may need to do a TO_CHAR on the date field. If so, I would look at other dates in the SELECT clause of the SQL generated by PS Query and use the same format they do.