RE: [sqr-users] RE: Date Validation

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

Don't forget that month and day cannot be zero and none of the 2 character
strings may include a minus sign or decimal point.  Also, testing day
against 31 is a good way to catch some invalid dates, but to catch them all,
we have to test for the actual last day of each month, including leap year
February.  I wrote the appropriate code a few days ago.

Another possibility is to set up an array or a string of 1231 positions.
Map a 0, 1, or 2 into each position based on whether the corresponding date
is invalid (May 32 = 532), valid (June 28 = 628), or possibly valid (Feb 29
= 229).  Extract the month and day in a single variable, $mmdd, and proceed
as follows:

move $mmdd to #mmdd
if translate($mmdd, '0123456789', '') <> '' 
or range(#mmdd, 101, 1231) = 0 
or valid.day(#mmdd) = 0 
or (#mmdd = 229 and mod(#year, 4) > 0)
  show $mmddyy ' is a bad date'
end-if

-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu]
Sent: Friday, June 18, 2004 9:29 AM
To: This list is for discussion about the SQR database reportinglanguage
fr omHyperion Solutions.
Subject: RE: [sqr-users] RE: Date Validation 



I haven't been paying much attention to this thread so if I'm repeating
something that has already been said, please forgive me.  

If the dates are 6 characters - 2 for the day, 2 for the month and 2 for
the year, this is cake.  Split up the string and convert the 2 char
parts to numbers.  If all parts convert without error (no letters or
special chars), then test that the day is <= 31, test that the month <=
12 and forget the year because any value will be OK.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Alexander, Steve
Sent: Friday, June 18, 2004 10:53 AM
To: 'This list is for discussion about the SQR database reportinglangu
age from Hyperion Solutions.'
Subject: RE: [sqr-users] RE: Date Validation 

Krishna, it was unclear whether you said there were 6 dates in each line
of
the input file or there were six characters in each date and one date
one
each line of the input file.  Also, are the dates within a limited range
of
time?  If the dates are six characters (no century) then there are only
36525 valid values repeated over and over for millions of dates.

Unfortuneately, SQR has no built-in date validation function, but there
may
be programming tricks that are simpler than the code I sent you.  What
can
you tell us about the dates?  How are they formatted and is there any
restriction to the values?



-----Original Message-----
From: James Womeldorf [mailto:jwomeldo@fastenal.com]
Sent: Friday, June 18, 2004 8:31 AM
To: 'This list is for discussion about the SQR database reporting
languagef rom Hyperion Solutions.'
Subject: RE: [sqr-users] RE: Date Validation 


Hi Krishna,
If there are a relatively small number of actual dates represented in
the
file (lots of duplicates) perhaps you could store validated dates in a
string which delimits them with some odd character that you would NEVER
find
in the date field.  Then, as a first test use INSTR() to see if a date
is in
the validated list.  If not there, then check using Oracle.  It should
cut
the number of database calls drastically.  
The only thing you would have to watch is that the validation string
length
did not exceed the maximum string length for SQR (32767).  If the string
length would be exceed then just avoid adding any more dates to it.
Jim Womeldorf



-----Original Message-----
From: Krishna [mailto:knookula@yahoo.com]
Sent: Friday, June 18, 2004 9:33 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] RE: Date Validation 


Well, currently I am validating all the dates from the input file by
sending
the dates one at a time to Oracle and checking them whether the dates
are
valid. If the the date is invalid then I am trapping the invalid date
from
the ON-ERROR procedure. 
   I am trying to see if there are any other better method of date
validations in SQR. Number of records in my input file like I said
earlier
is 2-3 million. The way I am currently achieving the task is by sending
a
request to Oracle six times one for each date, since I have to write an
error log specifying which date is invalid in the input file for every
record read from the input file. 

                
---------------------------------
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users