Re: SQL Date Math

Given: field refdt is 8-digit numeric representing YYYYMMDD
Apparently given scenario is a *QRYDFN tagged with *MDY [for date format], and it generates a character string of seven bytes in the form CCYYMMDD where always CC='20'. Ideally [not to imply it would live until 2100 ;-), but it is more clear to understand] that query would be done instead using the result field TodayChar = char(current(date),iso) to provide input to the result field Today2, to eliminate the '20'; and then making necessary corrections to the substring expressions [to use the *ISO formatted date string].

What makes the query even clearer is to define the conversion of the current date in a VIEW. Even better, the conversion can be into numeric so the DIGITS() can be omitted; i.e. conversion is all on the current date, into numeric, and no conversion on the existing numeric. The following is an example creating the original table as reference, and then the view with field naming to enable the existing record selection; i.e. it need not rename the fields that way. If the *QRYDFN is changed to reference the VIEW, then all of the result field definitions can be removed; the expressions to derive a compatible comparison are now encapsulated in the VIEW. That same VIEW can be referenced for any query that needs to perform selection involving current date and the field refdt. The expression could easily be replaced by a Function() as discussed in the other posts of this thread.

create table refdttable (refdt numeric (8, 0))
create view refdtvwhoy (today2, refdta) as
( select
cast( year(current date) * 10000
+ month(current date) * 100
+ day(current date) as numeric(8, 0) ) as today2
,refdt as refdta
from refdttable )

Regards, Chuck