Aah, I see. I'll need to check our UDT.OPTIONS. I think that I've managed to get this one worked out by creating a DICT entry without formating. This gave me the list that I need. In that we are migrating more and more data to SQL Server which really chokes on a bad date I'll need to get better at flushing these out.
Thanks, Greg From: Bill Haskett <[email protected]> To: U2 Users List <[email protected]> Date: 01/29/2013 06:17 PM Subject: Re: [U2] Searching for 'Bad' Dates Sent by: [email protected] Greg: That was the point. What happens is an externally formatted date isn't OCONV'd, if the dbms is configured to return an empty string for a bad conversion. I've got this set up on our systems like this. So, when I run the following code: 2 Dev (0)-> BPTEST CRT 'OConverted Date--: ' : OCONV('01/29/13', 'D') ; END Compiling Unibasic: SAVEDLISTS\BpTest_547690521 in mode 'p'. compilation finished OConverted Date--: ...I get what you're looking for, an empty string for dates filed in external format. In UniData I need UDT.OPTIONS 56 ON. Bill ------------------------------------------------------------------------ ----- Original Message ----- *From:* [email protected] *To:* U2 Users List <[email protected]> *Date:* 1/29/2013 3:09 PM *Subject:* Re: [U2] Searching for 'Bad' Dates > Bill, > > This is only returning those records that do not have a value in > MPAR.STATUS.DATE. Unfortunately I looking for records where an external > date has been written instead of the internal date. > > Greg > > > > From: Bill Haskett <[email protected]> > To: U2 Users List <[email protected]> > Date: 01/29/2013 06:01 PM > Subject: Re: [U2] Searching for 'Bad' Dates > Sent by: [email protected] > > > > Greg: > > Can you do something like: > > :select {FileName} WITH EVAL "OCONV(MPAR.STATUS.DATE, 'D')" = "" > > This works fine on UniData in PICK mode, where an invalid conversion > returns a null (an empty string). > > HTH, > > Bill > > ------------------------------------------------------------------------ > > -----Original Message----- > From: [email protected] [ > mailto:[email protected]] On Behalf Of Greg Coelho > Sent: Wednesday, 30 January 2013 8:38 > To: [email protected] > Subject: Re: [U2] Searching for 'Bad' Dates > > All, > > Can anyone help me with a query to find instances where an external date > has been written to a date field? I would like to use ...WITH > MPAR.STATUS.DATE LIKE ".. > ./..." (the dates would be in the fomat: '12/16/12', but this field is set > with external formatting so I end up bringing back everything. Is there a > way I can 'turn off' formatting for this field within the query? > > Thanks, > > Greg > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
