Focal Point
WHERE stmt using HYYMDS date format

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9181097331

August 25, 2004, 07:00 PM
<bapper>
WHERE stmt using HYYMDS date format
I am trying to use a where stmt and compare my oracle date field (ACCT_D) to today's system date or yesterday, or the day before, etc. The field is in HYYMDS format. It looks like this 2004/08/24 00:00:00AM

I need to say something like "WHERE ACCT_D EQ &YYMD", but this does not work.

Can anybody help?
August 25, 2004, 07:09 PM
susannah
You need to get your system variable into the same format, so the only way i know to do that is to define a variable in the ds you're reading:

NEWTIME/HYYMDIA WITH somevar=DT(&YYMD 00:00AM);
NICEDAY/HYYMDS = NEWTIME;

Then your variable NICEDAY can be compared to your db variable, both in dt format, if that's what you need to do.
August 25, 2004, 07:21 PM
<Pietro De Santis>
You could do this:

-SET &FR_DT = EDIT(&YYMD,'9999-99-99') || '-00.00.00.000000';
-SET &TO_DT = EDIT(&YYMD,'9999-99-99') || '-23.59.59.999999';

WHERE ACCT_D FROM '&FR_DT' TO '&TO_DT'
August 25, 2004, 07:48 PM
Tom Walker
As you know, the Oracle date type is really a date-time-stamp. If you want WebFocus to treat the Oracle date field as format YYMD/DATE instead of format HYYMDS, you can simply add the following line to your server profile (EDASPROF.PRF) ...

SQL SQLORA SET DATETIME OFF
August 26, 2004, 03:02 PM
jimster06
In an application where selection is done by comparing an H-format date to an &VAR such as
YYYY/MM/DD HH:MM:SS.mmmmmm
, give this a shot:

WHERE H_FORMAT_DATE GE DT (&VAR)

Note the DT, it tells WebFOCUS (or FOCUS) to make the proper comparison.

HTH