![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
<Jimmy> |
Anybody??? I'm desparate. Thanq | ||
|
Member |
I have some dates in my system also that are 00/00/0000 (just different format). So I was trying to figure out something that may help. I have a couple questions.... I'm assuming you have some dates that are actually null and you need to keep them that way, correct? Is there another field that you could concat to the date make it unique and then query on that? This could work I think. For instance in my database I could concat the '0000/00/00' date to aonther field that wouldn't be there for a date that was actually null. Hope this makes sense. I'll keep trying... | |||
|
Expert |
See if i understand you correctly. Focus uses SMART dates, they are serial dates, just like in Excel, the number of days elapsed since 1/1/1900 or 12/31/1899 until the specified date. If your specified date comes in as 00 00 0000, then focus tries to make a serial date out of it and cant. neither can excel, i don't believe. so you can make a second date in your database BEFORE you translate your legacy date into a SMART date, and make that date the Alpha equivalent(A8)... or even A10 00/00/0000 if you really want to keep the / marks. then do your testing against this new variable, while keeping the SMART date for date calculations and other cool date stuff. I do it this way for data from a legacy system which allows Feb 31st to be entered as valid! [yikes! No validation, so i have to check the actual alpha entry.] Works for me, hope it helps. | |||
|
<Jimmy> |
Thanks Victoria and Susannah. I appreciate your efforts. Ufortunately, my date values of 0000/00/00 are absolutely valid and not null or blank or missing, just that value. So when I add my WHERE clause, I might want to restrict the records returned by entering 'WHERE xdate EQ 0000/00/00' and I want back all records where that condition exists. You're right about the base date and WF wants to convert my 0 dates to 1900/12/31, which is the base date for WF (or so I'm told by NY). If I include a date field in my report and it comes across a record with a date of 0000/00/00, the value is assumed to be MISSING and my NODATA is printed. Even NY is having fits with this one. Unfortunately, I cannot add fields to the table either because it's an off-the-shelf application and database and I don't have access to the structure. If anyone has any ideas, I'd truly appreciate it. Jimmy | ||
|
Expert |
Jimmy, you need to edit the application to define a virtual variable, this is the stuff focus is the world's best at fixing. But unless you can 'access the app', there's nothing you can do. | |||
|
<mhuber> |
I understand that 0000/00/00 is perfectly valid, however it appears that WebFOCUS thinks the value is null. Consequently, one of these should work for your WHERE: WHERE xdate IS MISSING; WHERE xdate EQ MISSING; If that works, then this should work when you display the date field in a report: COMPUTE NEWDATE/A10 = IF xdate EQ MISSING THEN '0000/00/00' ELSE EDIT(DATECVT(xdate, 'YYMD', 'A8YYMD'), '9999/99/99'); If the WHERE works, but not the COMPUTE, we might need to tweak things a bit. If the where doesn't even work... well, let's not go there yet. ![]() Let me know how things work out. Regards, Michael | ||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|