I have a column called: BillDate in my table. Its format is: HYYMDs.


I want a where condition:
WHERE BillDate <= TODAY[)

Do I define an item: TodaysDate using the today function? What would that look like?

No luck so far....

This message has been edited. Last edited by: FP Mod Chuck,

Robert

This document may help you. https://techsupport.informatio...wbf/wbf_tcn_143.html

thanks, I could no doubt go thru the support center...was thinking this was something folks do all the time and might have the syntax for me.

I haven't done it with a full date/timestamp before or I would have provided an example.

for some reason it wont let me format TODAY('HYYMD') for comparison.

This message has been edited. Last edited by: RobertF,

ehh...simplest things take too much time.

This message has been edited. Last edited by: RobertF,

TODAY is for standard date fields. for HYYMDs fields you should use HGETC. That will give yoy the date and time in a HYYMDS field whcih you can use for whatever purpose..

You could try:

WHERE BillDate LE '&DATEYYMD 23:59:59.999'  

You could try

WHERE BillDate LE DT(&YYMD|23599999)

Can't remember if its a string or number

I recently (sometime after our 82x upgrade) noticed that the following code will work:

WHERE RUN_DATE GE '&YYMD'

RUN_DATE defined as HYYMDS. The SQL that gets produced is:

WHERE
(T1."RUN_DATE" >= CAST('20201125 00:00:00' AS DATETIME));

This test is against SQL Server, but I am pretty sure it also works versus DB2. I am pretty sure that this did not always work, but it does now. Use of a literal, instead of the & variable, also works.

Nicely- the following code fails:

WHERE RUN_DATE GE '&MDYY'

the trace shows-

(FOC177) INVALID DATE CONSTANT: 11252020