RE: Diagnostic CPD4019 message

You're formatting date 2 like dd/mm/yy. Is that intentional?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Greenhough Nick DWP
Sent: Thursday, May 18, 2006 10:09 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Diagnostic CPD4019 message 

We have been having problems with a query continually failing with the
CPD4019 message.

It is a simple query where we want to select certain records which meet
certain criteria from different tables and have a calculated date RESULT is
less than or equal to zero. The CPD message refers to DATEA. These are the
result fields we define:

DATE1 - DIGITS(DATEA)

DATE2 - SUBSTR(DATE1,7,2)||'/'||
        SUBSTR(DATE1,5,2)||'/'||
        SUBSTR(DATE1,3,2)

NEWDATE - DATE(DATE2)

TODAY1 - DIGITS(TODAYS DATE)

TODAY2 - SUBSTR(TODAY1,7,2)||'/'||
         SUBSTR(TODAY1,5,2)||'/'||
         SUBSTR(TODAY1,3,2)

NEWTODAY - DATE(TODAY2)

RESULT - DAYS(NEWDATE) - DAYS(NEWTODAY)

I have checked the database table and all entries in the column DATEA are
valid dates in the format yyyymmddd (numeric) although some users have
entered dates with the year as 3005 and 9200 (is there an upper system date
beyond which it cannot calculate?)


The query attempts to write certain fields away to another DB table but
fails every time. I have replaced dates that did not correspond with the
format yyyymmdd with a single date 18500101 (1st Jan 1850). The field can
and does contain zero in some circumstances

Can anyone help?

Cheers

Nick

Nick Greenhough
Configuration and Business Analyst
Debt Manager Project
Tel   0113 230 9239 (Lawnswood)

Nick Greenhough
Configuration and Business Analyst
Debt Manager Project
Tel   0113 230 9239 (Lawnswood)

**********************************************************************
This document is strictly confidential and is intended only for use by the
addressee. 
If you are not the intended recipient, any disclosure, copying, distribution
or other 
action taken in reliance of the information contained in this e-mail is
strictly prohibited.
Any views expressed by the sender of this message are not necessarily those
of the Department 
for Work and Pensions.
If you have received this transmission in error, please use the reply
function to tell us 
and then permanently delete what you have received.
Please note: Incoming and outgoing e-mail messages are routinely monitored
for compliance 
with our policy on the use of electronic communications.
**********************************************************************


The original of this email was scanned for viruses by Government Secure
Intranet (GSi)  virus scanning service supplied exclusively by Cable &
Wireless in partnership with MessageLabs.
On leaving the GSI this email was certified virus free.
The MessageLabs Anti Virus Service is the first managed service to achieve
the CSIA Claims Tested Mark (CCTM Certificate Number 2006/04/0007), the UK
Government quality mark initiative for information security products and
services.  For more information about this please visit www.cctmark.gov.uk