Question

Lobby Date Parameter

  • 6 December 2021
  • 3 replies
  • 762 views

Userlevel 4
Badge +10

Hello

I believe i have some very basic question. 
How can i change Lobby Page parameter #TODAY# to pass/display date as MM/DD/YYYY, 
there must be some setting i am missing , when i try to overwrite default today’s date with correct format i still cannot get correct query result in the elements.
here are my settings
 

2- How to fix condition in following to get default #today# from page or let the user enter any other date to pass . In following example i am expecting Planned orders = zero and Created today = 361 (as of 1/14/2021)

 


This topic has been closed for comments

3 replies

Userlevel 6
Badge +12

I am not sure about the Lobby question, but a quick word on dates (as related to your Question 2)…

I am not sure why you are using NVL() on TRUNC(SYSDATE), as SYSDATE will never be null. And when comparing dates you can simply use a equals sign. For example, this gathers all invoices from yesterday:

SELECT * FROM Man_Supp_Invoice WHERE TRUNC(Invoice_Date) = TRUNC(SYSDATE - 1)

Straight date comparison with an equals sign, the same way you would directly compare strings or numbers.

Another handy function you might want to familiarize yourself with is TO_CHAR(), as it is what is used to convert numbers and dates to strings (which you can then compare). For example, I could do what I did above via TO_CHAR with something like this:

SELECT TO_CHAR(Invoice_Date, 'MM/DD/YYYY') AS ToCharDate, MSI.*
FROM Man_Supp_Invoice MSI
WHERE TO_CHAR(Invoice_Date, 'MM/DD/YYYY') = TO_CHAR(SYSDATE - 1, 'MM/DD/YYYY')

Note that if I used dissimilar format masks in TO_CHAR() on either side of the comparison, I would not get any matches. The default format mask appears to be DD-MON-YY, though that may be controlled by regional settings on the Oracle instance or client side (not sure).

TRUNC() and TO_CHAR() can end up doing the same thing for you: isolating the date portion of what is actually a date/time field, for both displaying and comparing dates.

Unfortunately, I am not able to use an expression in the Default Value of a Lobby data field (with regard to your Question 1). So, you have a good question there -- how can one change the format of the field a user will enter when it comes to dates? I have not created any Lobbies using a date field as a Data element, but probably will at some point. It would be good to know how to control the formatting on that…

 

Thanks,

Joe Kaufman

Userlevel 4
Badge +10

@sutekh137 thanks for pointing that sysdate will never be null, i soon realized after posting that. I tried date mask in my condition and i can get results  when user input a date but not with #Today# , the default value. so far i am getting either or. sysdate (default)or user entered date.

Userlevel 6
Badge +12

@sutekh137 thanks for pointing that sysdate will never be null, i soon realized after posting that. I tried date mask in my condition and i can get results  when user input a date but not with #Today# , the default value. so far i am getting either or. sysdate (default)or user entered date.

 

When you say you cannot get results, is that because when you try to match on date for a user-entered value you use the mask “MM/DD/YYYY”, but #TODAY# is coming back as “YYYY-MM-DD”?

You are going to have to match by using the counterpart to TO_CHAR(), called TO_DATE(). And you will need to check both formats “MM/DD/YYYY” and “YYYY-MM-DD”. Here is an example with hard-coded dates where I am looking for AP invoices across Dec 6 and Dec 7:

 

SELECT TO_CHAR(Invoice_Date, 'MM/DD/YYYY') AS ToCharDate, MSI.*
FROM Man_Supp_Invoice MSI
WHERE Invoice_Date = TO_DATE('12/06/2021', 'MM/DD/YYYY')
OR Invoice_Date = TO_DATE('2021-12-07', 'YYYY-MM-DD')

 

If the hard-coded string literals ‘12/06/2021’ and ‘2021-12-07’ were replaced with the Lobby page parameter DATE_ENTERED, then you will get a match as long as the date matches and is in one format or the other.

This should have the advantage of not only making the default #TODAY# work, it means that user’s can enter a date parameter manually in MM/DD/YYYY or YYYY-MM-DD format and it should still find matches.

Hopefully you can see what I mean by using TO_DATE() and an OR on your WHERE clause…

 

Thanks,

Joe Kaufman